巢狀式觸發

解析朋友的需求(用 SQL Server 2012 實驗):

use tempdb

create table t(c1 int primary key,c2 varchar(10),c3 datetime)
go
create table log(id int identity,[Insert Log 的時間] datetime,operation char(1),c1 int,c2 varchar(10),[Update 紀錄的時間] datetime)
go

create trigger t1 on t
for insert,delete,update
as
update t set c3=getdate() from t join inserted i on t.c1=i.c1
go

create trigger t2 on t
for insert,delete,update
as
insert log([Insert Log 的時間],operation,c1,c2,[Update 紀錄的時間]) select getdate(),’d’,* from deleted
insert log([Insert Log 的時間],operation,c1,c2,[Update 紀錄的時間]) select getdate(),’i’,* from inserted
waitfor delay ’00:00:00.003′
go

exec sp_settriggerorder @triggername=’t2′,@order=’first’,@stmttype=‘update’
go

insert t values(1,’a’,null),(2,’b’,null)
select null,null,’先 insert log’,null,null,null union all select * from log
truncate table log
update t set c2+=’2′ where c1=1
select * from log

–exec sp_settriggerorder @triggername=’t2′,@order=’none’,@stmttype=‘update’

drop table t
drop table log
go

create table t(c1 int,c2 varchar(10),c3 datetime)
go
create table log(id int identity,[Insert Log 的時間] datetime,operation char(1),c1 int,c2 varchar(10),[Update 紀錄的時間] datetime)
go

create trigger t1 on t
for insert,delete,update
as
update t set c3=getdate() from t join inserted i on t.c1=i.c1
go

create trigger t2 on t
for insert,delete,update
as
insert log([Insert Log 的時間],operation,c1,c2,[Update 紀錄的時間]) select getdate(),’d’,* from deleted
insert log([Insert Log 的時間],operation,c1,c2,[Update 紀錄的時間]) select getdate(),’i’,* from inserted
waitfor delay ’00:00:00.003′
go

exec sp_settriggerorder @triggername=’t1′,@order=’first’,@stmttype=’update’
go

insert t values(1,’a’,null),(2,’b’,null)
select null,null,’先 update 日期’,null,null,null union all select * from log
truncate table log
update t set c2+=’2′ where c1=1
select * from log
go

drop table t
drop table log

 

透過時間串聯,以及資料的變異觀察:

  • 未定義 Insert 觸發 Trigger 的順序時,不管定義 Update 語法哪個 Trigger 先執行,Insert 到另一個 table 的 log 一定最後做
  • Update 時,有分 Trigger 先後

 

image

同樣的語法,但指定 Insert 語法觸發 Trigger 的先後順序,不指定 Update:

exec sp_settriggerorder @triggername=’t2′,@order=’first’,@stmttype=‘insert’

則結果如下:

image

將兩者比較,可以看到未設定Insert、Update語法觸發的 trigger 誰先誰後執行時,t1 先執行,t2 後執行

發表迴響

在下方填入你的資料或按右方圖示以社群網站登入:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / 變更 )

Twitter picture

You are commenting using your Twitter account. Log Out / 變更 )

Facebook照片

You are commenting using your Facebook account. Log Out / 變更 )

Google+ photo

You are commenting using your Google+ account. Log Out / 變更 )

連結到 %s

%d 位部落客按了讚: