22,209
社区成员
发帖
与我相关
我的任务
分享
--种子表 num_tb
if object_id('num_tb') is not null
drop table num_tb
go
create table num_tb(d datetime,id int)
insert num_tb select getdate(),1
if object_id('tb') is not null
drop table tb
go
create table tb(id varchar(20),name varchar(10))
create clustered index idx_clu_tb on tb(id)
go
create trigger tri_tb on tb
INSTEAD OF INSERT
as
begin
set nocount on
declare @i int,@id varchar(20),@j int
select @i=count(*) from inserted
begin tran
update num_tb with(TABLOCKX) set
id=(case when convert(char(8),d,112)=convert(char(8),getdate(),112)
then id+@i else @i end),
@j=(case when convert(char(8),d,112)=convert(char(8),getdate(),112) then id else 0 end),
d=getdate()
commit tran
select * into #t from inserted
update #t set id=convert(varchar(8),getdate(),112)+right('00000'+rtrim(@j),5),@j=@j+1
insert tb select * from #t
end
go
--插入记录测试
insert into tb(name) values('张三')
insert into tb(name) values('李四')
select * from tb
/*
id name
-------------------- ----------
2010012700002 张三
2010012700003 李四
*/
insert into tb select '2010012700003','王五'
/*
id name
-------------------- ----------
2010012700002 张三
2010012700003 李四
2010012700004 王五
*/