create table tb(colID int identity,status int,ID nvarchar(14))
go
create trigger test on tb
for insert,update
as
begin
if update(status)
begin
declare @status int,@colID int
select @status = status,@colID = colID from inserted
if @status = 1
begin
declare @Nowdate nvarchar(8),@OtherDate nvarchar(8),@Num nchar(13),@ID int
select @Nowdate = convert(char(8),getdate(),112),@OtherDate = '19990101',@ID = 0,@Num = ''
select @OtherDate = substring(ID,1,8),@ID = right(ID,4) from tb where substring(ID,1,8) = @Nowdate
if @OtherDate <> @Nowdate
begin
select @ID = 0
end;
select @Num = @Nowdate + right((10000 + @ID + 1),4)
update tb set id = @Num where colID = @colID
end;
end;
end;
go
insert into tb(status) select 1
select * from tb