--生成流水号--创建测试表 create table test(id varchar(18), --流水号,日期(8位)+时间(4位)+流水号(4位) name varchar(10) --其他字段 )go --创建生成流水号的触发器 create trigger t_insert on test INSTEAD OF insert as declare @id varchar(18),@id1 int,@head varchar(12) select * into #tb from inserted set @head=convert(varchar,getdate(),112)+replace(convert(varchar(5),getdate(),108),':','') select @id=max(id) from test where id like @head+'%' if @id is null set @id1=0 else set @id1=cast(substring(@id,13,4) as int) update #tb set @id1=@id1+1 ,id=@head+right('0000'+cast(@id1 as varchar),4) insert into test select * from #tb go --插入数据,进行测试 insert into test(name) select 'aa' union all select 'bb' union all select 'cc'--修改系统时间,再插入数据测试一次 insert into test(name) select 'aa' union all select 'bb' union all select 'cc'--显示测试结果 select * from test --删除测试环境 drop table test/*--测试结果 id name ------------------ ---------- 2004022720430001 aa 2004022720430002 bb 2004022720430003 cc 2004022720430004 aa 2004022720430005 bb 2004022720430006 cc(所影响的行数为 6 行) --*/
--根据输入类别自动生成编号示例--表 create table tab(a varchar(20),b varchar(100)) go--触发器 create trigger tr_insert on tab instead of insert as declare @dt varchar(10) set @dt='-'+convert(varchar(10),getdate(),120)select * into #t from inserted a join( select gid_new=b.a,sid_new=1000001+isnull(max(cast(right(a.a,5) as int)),0) from tab a right join inserted b on charindex(b.a+@dt,a.a)=1 group by b.a )b on a.a=b.gid_new order by b.gid_newdeclare @nid varchar(100),@a int update #t set @a=case @nid when gid_new then @a+1 else sid_new end ,a=gid_new+@dt+'-'+right(@a,5) ,@nid=gid_newinsert tab select a,b from #t go--插入数据 insert tab values('sj','2222') insert tab select 'sj','324324' union all select 'sj','33343' union all select 'dj','33343' union all select 'dj','24324' union all select 'sj','24234'--显示插入结果 select * from tab order by a go--删除测试 drop table tab/*--测试结果a b -------------------- -------- dj-2004-07-1-00001 33343 dj-2004-07-1-00002 24324 sj-2004-07-1-00001 2222 sj-2004-07-1-00002 324324 sj-2004-07-1-00003 33343 sj-2004-07-1-00004 24234(所影响的行数为 6 行) --*/
--创建自定义函数,得到新的ID create function f_getid( @YXDM varchar(3), @zydm varchar(4) ) returns int as begin declare @re int
select @re=right(id,4) from( select id=max(id) from tb where yxdm=@yxdm and zydm=@zydm ) a set @re=isnull(@re,0)+1 return(@re) end go --创建测试表 create table tb(id varchar(12) ,YXDM varchar(3),ZYDM varchar(4)) go--创建触发器,生成ID号 create trigger t_insert on tb instead of insert as select * into #t from inserted order by yxdm,zydm declare @yxdm varchar(3),@zydm varchar(4),@id intupdate #t set @id=case when @yxdm=yxdm and @zydm=zydm then @id+1 else dbo.f_getid(yxdm,zydm) end ,id='GY'+right(yxdm,2)+@zydm+right('0000'+cast(@id as varchar),4) ,@yxdm=yxdm,@zydm=zydminsert into tb select * from #t go--插入数据测试 insert into tb(YXDM,ZYDM) values('001','0001') insert into tb(YXDM,ZYDM) values('001','0001') insert into tb(YXDM,ZYDM) values('001','0002') insert into tb(YXDM,ZYDM) values('001','0002') insert into tb(YXDM,ZYDM) values('001','0001') insert into tb(YXDM,ZYDM) values('001','0003') insert into tb(YXDM,ZYDM) values('001','0003') insert into tb(YXDM,ZYDM) values('002','0001') insert into tb(YXDM,ZYDM) values('002','0001') insert into tb(YXDM,ZYDM) values('002','0001')--显示结果 select * from tb order by idgo --删除测试环境 drop table tb drop function f_getid
create table test(id varchar(18), --流水号,日期(8位)+时间(4位)+流水号(4位)
name varchar(10) --其他字段
)go
--创建生成流水号的触发器
create trigger t_insert on test
INSTEAD OF insert
as
declare @id varchar(18),@id1 int,@head varchar(12)
select * into #tb from inserted
set @head=convert(varchar,getdate(),112)+replace(convert(varchar(5),getdate(),108),':','')
select @id=max(id) from test where id like @head+'%'
if @id is null
set @id1=0
else
set @id1=cast(substring(@id,13,4) as int)
update #tb set @id1=@id1+1
,id=@head+right('0000'+cast(@id1 as varchar),4)
insert into test select * from #tb
go
--插入数据,进行测试
insert into test(name)
select 'aa'
union all select 'bb'
union all select 'cc'--修改系统时间,再插入数据测试一次
insert into test(name)
select 'aa'
union all select 'bb'
union all select 'cc'--显示测试结果
select * from test
--删除测试环境
drop table test/*--测试结果
id name
------------------ ----------
2004022720430001 aa
2004022720430002 bb
2004022720430003 cc
2004022720430004 aa
2004022720430005 bb
2004022720430006 cc(所影响的行数为 6 行)
--*/
create table tab(a varchar(20),b varchar(100))
go--触发器
create trigger tr_insert on tab
instead of insert
as
declare @dt varchar(10)
set @dt='-'+convert(varchar(10),getdate(),120)select * into #t
from inserted a join(
select gid_new=b.a,sid_new=1000001+isnull(max(cast(right(a.a,5) as int)),0)
from tab a
right join inserted b on charindex(b.a+@dt,a.a)=1
group by b.a
)b on a.a=b.gid_new
order by b.gid_newdeclare @nid varchar(100),@a int
update #t set @a=case @nid when gid_new then @a+1 else sid_new end
,a=gid_new+@dt+'-'+right(@a,5)
,@nid=gid_newinsert tab select a,b from #t
go--插入数据
insert tab values('sj','2222')
insert tab select 'sj','324324'
union all select 'sj','33343'
union all select 'dj','33343'
union all select 'dj','24324'
union all select 'sj','24234'--显示插入结果
select * from tab order by a
go--删除测试
drop table tab/*--测试结果a b
-------------------- --------
dj-2004-07-1-00001 33343
dj-2004-07-1-00002 24324
sj-2004-07-1-00001 2222
sj-2004-07-1-00002 324324
sj-2004-07-1-00003 33343
sj-2004-07-1-00004 24234(所影响的行数为 6 行)
--*/
create function f_getid(
@YXDM varchar(3),
@zydm varchar(4)
)
returns int
as
begin
declare @re int
select @re=right(id,4) from(
select id=max(id) from tb where yxdm=@yxdm and zydm=@zydm
) a
set @re=isnull(@re,0)+1
return(@re)
end
go
--创建测试表
create table tb(id varchar(12)
,YXDM varchar(3),ZYDM varchar(4))
go--创建触发器,生成ID号
create trigger t_insert on tb
instead of insert
as
select * into #t from inserted order by yxdm,zydm
declare @yxdm varchar(3),@zydm varchar(4),@id intupdate #t set @id=case when @yxdm=yxdm and @zydm=zydm then @id+1 else dbo.f_getid(yxdm,zydm) end
,id='GY'+right(yxdm,2)+@zydm+right('0000'+cast(@id as varchar),4)
,@yxdm=yxdm,@zydm=zydminsert into tb
select * from #t
go--插入数据测试
insert into tb(YXDM,ZYDM) values('001','0001')
insert into tb(YXDM,ZYDM) values('001','0001')
insert into tb(YXDM,ZYDM) values('001','0002')
insert into tb(YXDM,ZYDM) values('001','0002')
insert into tb(YXDM,ZYDM) values('001','0001')
insert into tb(YXDM,ZYDM) values('001','0003')
insert into tb(YXDM,ZYDM) values('001','0003')
insert into tb(YXDM,ZYDM) values('002','0001')
insert into tb(YXDM,ZYDM) values('002','0001')
insert into tb(YXDM,ZYDM) values('002','0001')--显示结果
select * from tb order by idgo
--删除测试环境
drop table tb
drop function f_getid