sqlserver使用嵌套觸發器

如果一個觸發器在執行操作時引發了另一個觸發器，而這個觸發器又接著引發下一個觸發器……這些觸發器就是嵌套觸發器。觸發器可嵌套至 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