Create Table Num_TB (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 tbINSTEAD OF INSERTasbeginset nocount ondeclare @i int, @ id varchar (20), @ j intselect @ i = count (*) from insertedbegin tranif exists (select 1 from num_tb where convert (char (8), d, 112 ) = convert (25), getdate (), 112)) Beginselect @ J = ID from num Num_TB with (Tablockx) Update Num_TB with (Tablockx) set id = id @ i endelsebeginupdate num_tb with (Tablockx) SET D = Getdate ), ID = @ i set @ j = 0ndcommit translection * INTO #T from insertedupdate #t set id = convert (varchar (8), getdate (), 112) Right ('00000' RTRIM (@J), 5 ), @ j = @ J 1insert Tb Select * from #tendgo
alter trigger tri_tb on tbINSTEAD OF INSERTasbeginset nocount ondeclare @i int, @ id varchar (20), @ j intselect @ i = count (*) from insertedbegin tranupdate num_tb with (TABLOCKX) set id = (case when convert (char (8) , D, 112) = Convert (25), getdate (), 112) THEN ID @ i else @i end), @ J = (Case When Convert (CAAR (8), D, 112) = Convert (charr (8), getDate (), 112) THEN ID ELSE 0 end), D = getdate () commit transfer = connection = convert (varchar (8), getdate (), 112) Right ('00000' RTRIM (@J), 5), @ J = @ J 1insert Tb Select * from # TENDGO - Create a table
Go
- Insert Record Test Declare @i Intset @ i = 0WHILE @i <= 10000beginInsert INTO TB (Name) VALUES ('Zhang 3') Insert INTO TB (Name) Select 'Zhang Four' Union All Select 'Zhang 5' Union All SELECT 'Zhang Ji' Union All Select 'Zhang 8' Union All Select 'Zhang Jiu' Union All Select 'Zhang 10' Set @ i = @ i 1ENDGO - Delete Environment DROP TABLE TB
