Custom serial number trigger j9988 T-SQL

Drop table DBO. num_tbdrop table DBO. tbcreate table num_tb (d datetime, Id INT) insert num_tbselect '2017-01-01 ', 1 create table [TB] ([autoid] [bigint] Identity (2004) not null, [ID] [varchar] (20) null, [name] [varchar] (10) null, [createtime] [datetime] default getdate (), constraint [pk_tb] primary key clustered ([autoid] ASC) on [primary] create unique nonclustered index idx_id_tb on TB (ID) gocreate trigger tri_tb on tbinstead of insertasbegin set nocount on declare @ I int --, @ ID varchar (20), @ J int select @ I = count (*) from inserted begin Tran declare @ now varchar (14), @ latest varchar (14) Update num_tb with (tablockx) set @ now = Replace (replace (convert (varchar (19), getdate (), 120 ),'-',''),'',''), ':', ''), @ latest = Replace (replace (convert (varchar (19), D, 120 ),'-',''),'', ''), ':',''), id = (case when @ latest = @ now then ID + @ I else @ I end ), @ J = (case when @ latest = @ now then Id else 0 end), D = getdate () commit Tran select * into # T from inserted update # t set id = @ now + right ('000000' + rtrim (@ J), 6 ), @ J = @ J + 1 insert Tb (ID, name) Select ID, name from # tendgo -- Create Table go -- insert Record Test declare @ I intset @ I = 0 while @ I <= 1000 begin insert into Tb (name) values ('zhang san ') insert into Tb (name) select 'zhang si' Union all select' Zhang wu'union all select' Zhang liu'union all select' Zhang qi' Union all select' Zhang ei'union all select' Zhang jiu' Union all select 'ten sheets 'set @ I = @ I + 1 endselect max (ID ), min (ID), count (*), max (autoid), min (autoid), max (createtime), min (createtime) from tbgroup by left (ID, 14) order by 3 descselect * from TB awhere exists (select 1 from TB where ID>. ID and autoid <. autoid) -- truncate table TB