j9988 按日期生成自增号!

create table num_tb(d datetime,id int)
insert num_tb select '2004-01-01',1

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
if exists(select 1 from num_tb where convert(char(8),d,112)=convert(char(8),getdate(),112))
begin
select @j=id from num_tb with(TABLOCKX)
update num_tb with(TABLOCKX) set id=id+@i
end
else
begin
update num_tb with(TABLOCKX) set d=getdate(),id=@i
set @j=0
end
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

alter 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
--创建表

go     

--插入记录测试
declare @i int
set @i=0
while @i<=10000
begin
insert into tb(name) values('张三')
insert into tb(name) select '张四'
union all select '张五'
union all select '张六'
union all select '张七'
union all select '张八'
union all select '张九'
union all select '张十'
set @i=@i+1
end

 

 

go
--删除环境
drop table tb