j9988 按日期生成自增号!

最新推荐文章于 2024-11-12 18:12:53 发布

原创最新推荐文章于 2024-11-12 18:12:53 发布 · 1.5k 阅读

0 ·

本内容遵循CC 4.0 BY-SA版权协议

收录于

DataBase

博客主要围绕数据库操作展开，使用Go语言创建了num_tb和tb表，为tb表创建了聚集索引和触发器。触发器用于在插入数据时更新num_tb表的记录，并对插入的id进行处理。最后进行了插入记录测试，插入大量数据后删除了tb表。

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

--插入记录测试
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