SQL SERVER使用嵌套觸發器
2024-08-31 00:49:27
供稿：網友
，歡迎訪問網頁設計愛好者web開發。如果一個觸發器在執行操作時引發了另一個觸發器，而這個觸發器又接著引發下一個觸發器……這些觸發器就是嵌套觸發器。觸發器可嵌套至 32 層，并且可以控制是否可以通過"嵌套觸發器"服務器配置選項進行觸發器嵌套。
如果允許使用嵌套觸發器，且鏈中的一個觸發器開始一個無限循環，則超出嵌套級，而且觸發器將終止。
可使用嵌套觸發器執行一些有用的日常工作，如保存前一觸發器所影響行的一個備份。例如，可以在 titleauthor 上創建一個觸發器，以保存由 delcascadetrig 觸發器所刪除的 titleauthor 行的備份。在使用 delcascadetrig 時，從 titles 中刪除title_id ps2091 將刪除 titleauthor 中相應的一行或多行。要保存數據，可在 titleauthor 上創建 delete 觸發器，該觸發器的作用是將被刪除的數據保存到另一個單獨創建的名為 del_save 表中。例如：
create trigger savedel
on titleauthor
for delete
as
insert del_save
select * from deleted
不推薦按依賴于順序的序列使用嵌套觸發器。應使用單獨的觸發器層疊數據修改。
說明 由于觸發器在事務中執行，如果在一系列嵌套觸發器的任意層中發生錯誤，則整個事務都將取消，且所有的數據修改都將回滾。在觸發器中包含 print 語句，用以確定錯誤發生的位置。
遞歸觸發器
觸發器不會以遞歸方式自行調用，除非設置了 recursive_triggers 數據庫選項。有兩種不同的遞歸方式：
直接遞歸
即觸發器激發并執行一個操作，而該操作又使同一個觸發器再次激發。例如，一應用程序更新了表 t3，從而引發觸發器 trig3。trig3 再次更新表 t3，使觸發器 trig3 再次被引發。
間接遞歸
即觸發器激發并執行一個操作，而該操作又使另一個表中的某個觸發器激發。第二個觸發器使原始表得到更新，從而再次引發第一個觸發器。例如，一應用程序更新了表 t1，并引發觸發器 trig1。trig1 更新表 t2，從而使觸發器 trig2 被引發。trig2 轉而更新表 t1，從而使 trig1 再次被引發。
當將 recursive_triggers 數據庫選項設置為 off 時，僅防止直接遞歸。若要也禁用間接遞歸，請將 nested triggers 服務器選項設置為 0。
示例
a. 使用遞歸觸發器解決自引用關系
遞歸觸發器的一種用法是用于帶有自引用關系的表（亦稱為傳遞閉包）。例如，表 emp_mgr 定義了：
一個公司的雇員 (emp)。
每個雇員的經理 (mgr)。
組織樹中向每個經理匯報的雇員總數 (noofreports)。
遞歸 update 觸發器在插入新雇員記錄的情況下可以使 noofreports 列保持最新。insert 觸發器更新經理記錄的 noofreports 列，而該操作遞歸更新管理層向上其它記錄的 noofreports 列。
use pubs
go
-- turn recursive triggers on in the database.
alter database pubs
set recursive_triggers on
go
create table emp_mgr (
emp char(30) primary key,
mgr char(30) null foreign key references emp_mgr(emp),
noofreports int default 0
)
go
create trigger emp_mgrins on emp_mgr
for insert
as
declare @e char(30), @m char(30)
declare c1 cursor for
select emp_mgr.emp
from emp_mgr, inserted
where emp_mgr.emp = inserted.mgr
open c1
fetch next from c1 into @e
while @@fetch_status = 0
begin
update emp_mgr
set emp_mgr.noofreports = emp_mgr.noofreports + 1 -- add 1 for newly
where emp_mgr.emp = @e -- added employee.
fetch next from c1 into @e
end
close c1
deallocate c1
go
-- this recursive update trigger works assuming:
-- 1. only singleton updates on emp_mgr.
-- 2. no inserts in the middle of the org tree.
create trigger emp_mgrupd on emp_mgr for update
as
if update (mgr)
begin
update emp_mgr
set emp_mgr.noofreports = emp_mgr.noofreports + 1 -- increment mgr's
from inserted -- (no. of reports) by
where emp_mgr.emp = inserted.mgr -- 1 for the new report.
update emp_mgr
set emp_mgr.noofreports = emp_mgr.noofreports - 1 -- decrement mgr's
from deleted -- (no. of reports) by 1
where emp_mgr.emp = deleted.mgr -- for the new report.
end
go
-- insert some test data rows.
insert emp_mgr(emp, mgr) values ('harry', null)
insert emp_mgr(emp, mgr) values ('alice', 'harry')
insert emp_mgr(emp, mgr) values ('paul', 'alice')
insert emp_mgr(emp, mgr) values ('joe', 'alice')
insert emp_mgr(emp, mgr) values ('dave', 'joe')
go
select * from emp_mgr
go
-- change dave's manager from joe to harry
update emp_mgr set mgr = 'harry'
where emp = 'dave'
go
select * from emp_mgr
go
以下是更新前的結果：
emp mgr noofreports
------------------------------ ----------------------------- -----------
alice harry 2
dave joe 0
harry null 1
joe alice 1
paul alice 0
以下為更新后的結果：
emp mgr noofreports
------------------------------ ----------------------------- -----------
alice harry 2
dave harry 0
harry null 2
joe alice 0
paul alice 0
