触发器(trigger) 日期+流水=编号

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