SQL Server中trigger和變數的使用筆記

--用trigger插入主鍵的值--通過時間變數來決定主鍵的值create trigger ss on testinstead of insert --在插入之前執行此triggerasbegindeclare @index varchar(30)--聲明變數waitfor delay '00:00:00.010'--延時0.01秒執行下一條指令set @index = replace(replace(replace(replace(convert(varchar(23),getdate(),121),'-',''),' ',''),':',''),'.','') --將目前時間轉化為字串insert into test select @index,i.SNAME,i.STATUS,i.CITY from inserted iendinsert into test(SNAME,STATUS,CITY) values ('11',30,'ss');insert into test(SNAME,STATUS,CITY) values ('11',30,'ss');insert into test(SNAME,STATUS,CITY) values ('11',30,'ss');insert into test(SNAME,STATUS,CITY) values ('11',30,'ss');insert into test(SNAME,STATUS,CITY) values ('11',30,'ss');insert into test(SNAME,STATUS,CITY) values ('11',30,'ss');drop trigger ss;delete from testselect * from test--測試變數的用法declare @a numericset @a = cast(replace(replace(replace(replace(convert(varchar(23),getdate(),121),'-',''),' ',''),':',''),'.','') as numeric)print @aprint convert(varchar,(@a +1))--另一種方法使用變數declare @index varchar(30)--聲明變數set @index = replace(replace(replace(replace(convert(varchar(23),getdate(),121),'-',''),' ',''),':',''),'.','') --將目前時間轉化為字串print @index