drop proc usp_getnewid
go
CREATE proc usp_getnewid
@newdate datetime =null,
@varNewID varchar(9) output
as
set nocount on
begin
Declare @theM char(2),
@theD char(2),
@varMaxID char(10)
Declare @rc int
select @rc=0
if isnull(@newdate,0)=0
select @newdate=getdate()
if isdate(@newdate)=0
begin
select @rc=-2
return @rc
end
--get the maxid from the test table
if @rc=0
begin
select @varMaxID=(select isnull(max(cast(substring(number_order,5,5) as int)),'0')+1
from test with (rowlock)
where cast(substring(number_order,1,2) as int)=month(@NewDate))
select @rc=@@error
if @rc<>0 or isnull(@varMaxID,0)=0
begin
select @rc=-2
return @rc
end
declare @date datetime
set @date=getdate()
set @date=substring(@date,7,4)+left(@date,2)+substring(@date,4,2)
select cast(max(id) as int)+1
from table
where left(id,8)=@date
如果要判断某列是否为空。在触发器上加入这段代码。
………………
declare @variable_name type
select @variable_name=你要判断的列 from inserted
if @variable_name is null
begin
处理代码
end
else
begin
处理代码
end
………………
1:我先前比较忙,没有仔细验证,原来的程序修改如下(经验证,没有错误)
declare @m int,@d int
select @m=month(getdate()),@d=day(getdate())
declare @Str1 varchar(20),@Str2 varchar(20)
set @Str1=case when @m>9 then cast(@m as varchar(2))
else '0'+cast(@m as char(1)) end +
case when @d>9 then cast(@d as varchar(2))
else '0'+cast(@d as char(1)) end
--然后找出这天的最大id号
declare @id int
set @Str2='%'+@Str1+'%'
select @id=max(cast(substring(rtrim(lTrim(DateId)),5,5) as int))
from Table_DateID where DateId like @Str2
--设置现在的id号
set @id=isnull(@id,0)+1
--把新的id号转化成字符串
declare @i int,@idpref int
set @i=0
set @Str2=''
while @i<5
begin
set @idpref=(@id/power(10,@i)) % 10
set @i=@i+1
set @Str2=cast(@idpref as char(1))+isnull(@Str2,'')
end
set @Str1=@Str1+@Str2
select @Str1
2:在insert的时候该列为空则触发该时间
否则不出发该事件的写法:
在过程中加入一个判断
if field is not null--不为空
return--退出本过程
if field is null--为空
yourproc
我现在实现了这个过程
如下
Declare @theM char(2),@theD char(2),@theN char(5),@maxid char(10),@xxx char(9)
set @maxid=(select isnull(max(cast(substring(number_order,5,5) as int)),'0')+1 from test where
cast(substring(number_order,1,2) as int)=month(getdate()))
if not exists (select max(cast(substring(number_order,5,5) as int)) from test where
cast(substring(number_order,1,2) as int)=month(getdate()))
set @maxid='00001'
else
-- print '000000'
set @maxid=left('00000',5-len(@maxid))+@maxid
print @maxid
set @theM=Month(GetDate())
set @theD=Day(GetDate())
set @theM=left('00',2-Len(@theM))+@theM
set @theD=left('00',2-Len(@theD))+@theD
set @xxx=@theM+@theD+@maxid
print @xxx
insert into test (number_order) values (@xxx)
但是我又遇到一个新得问题
就是我现在需要得到得效果是
当INSERT得number_order字段得数值为空得时候触发该事件,不然不触发该事件
谢谢
对了。为什么这几天都没有看到大力啊
Create Trigger InsRow On 你的表名
For Insert
As
Declare @theM char(2),@theD char(2),@theN char(5)
Select @theM=Month(GetDate())
@theM=left('00',2-Len(@theM))+@theM
Select @theD=Day(GetDate())
@theD=left('00',2-Len(@theD))+@theD
Declare cursor cursor1 for select count(*)+1 from 你的表名 where left(maxid,4)=@theM+@theD
cursor1.open
fetch cursor1 into @theN
cursor1.close
@theN=left('00000',5-Len(@theN))+@theN
Update 你的表名 Set MaxID=@theM+@theD+@theN Where 能维一确定你的插入记录的条件(使用Inserted)
创建一个触发器:
Create Trigger InsRow On 你的表名
For Insert
As
Declare @theM char(2),@theD char(2),@theN char(5)
Select @theM=Month(GetDate())
Case Len(@theM)
When 1 Then @theM='0'+@theM
End
Select @theD=Day(GetDate())
Case Len(@theD)
When 1 Then @theD='0'+@theD
End
Declare cursor cursor1 for select count(*)+1 from 你的表名 where left(maxid,4)=@theM+@theD
cursor1.open
fetch cursor1 into @theN
cursor1.close
Case Len(@theN)
When 1 Then @theN='0000'+@theN
When 2 Then @theN='000'+@theN
When 3 Then @theN='00'+@theN
When 4 Then @theN='0'+@theN
End
Update Inserted Set MaxID=@theM+@theD+@theN
这样吧,方法可能麻烦:
--先得出日期的字段:
declare @m int,@d int
select @m=month(getdate()),@d=day(getdate())
declare @Str1 char(20),@Str2 char(20)
set @Str1=case when @m>9 then case(@m as char(2))
else '0'+case(@m as char(1)) end +
case when @d>9 then case(@d as char(2))
else '0'+case(@d as char(1)) end
--然后找出这天的最大id号
declare @id int
select @id=max(cast(substring(Fieldname,5,5) as int))
from yourtable where fieldname like @Str1
--设置现在的id号
set @id=@id+1
--把新的id号转化成字符串
declare @i int,@idpref int
set @i=0
while @i<5
begin
set @idpref=(@id/power(10,i))mod 10
set @i=@i+1
set @Str2=cast(@idpref as char(1))+isnull(Str2,'')
end
set @Str1=@Str1+@Str2
--得到的@Str1 就是你期望的字符串了
--祝好运