--根据输入类别自动生成编号示例
--表
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_new
declare @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_new
insert 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 行)
--*/
69.类别自动生成编号案例
最新推荐文章于 2023-07-28 19:01:58 发布