1 if exists(select * from sys.objects where name='test')
2 drop table test
3 go
4 create table test
5 (
6 id varchar(30), --流水号,日期(8位)+流水号(4位)
7 name varchar(10) --其他字段
8 )
9 go
10 create trigger t_insert on test
11 instead of insert
12 as
13 declare @id varchar(18),@id1 int,@head varchar(12)
14 select * into #tb from inserted
15 set @head=convert(varchar(20),getdate(),112)
16 select @id=max(id) from test
17 if @id is null
18 set @id1=0
19 else
20 set @id1=cast(substring(@id,9,4) as int)
21 update #tb set @id1=@id1+1,id=@head+right('0000'+cast(@id1 as varchar),4)
22 insert into test select * from #tb
23 go
24
25 insert into test(name) values('小黄')
26 insert into test(name) values('小程')
27 insert into test(name) values('小马')
28 select * from test
