使用触发器生成流水号 

 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

 

posted @ 2016-06-08 17:19  天启♘  阅读(436)  评论(0)    收藏  举报