建立巢狀觸發器
適用於：
SQL ServerAzure SQL 資料庫Azure SQL 受控執行個體
當觸發程序執行一個動作來啟動另一個觸發程序時，DML 和 DDL 觸發程序會形成巢狀。 這些動作可以起始化其他觸發程序等等。 DML 與 DDL 觸發程序最多可以巢狀方式嵌套多達 32 層。 您可以透過 [巢狀觸發程序] 伺服器組態選項來控制 AFTER 觸發程序是否可為巢狀。 不論此設定如何，INSTEAD OF 觸發程序（只有 DML 觸發程序可以作為 INSTEAD OF 觸發程序）均可為巢狀結構。
注意
從 Transact-SQL 觸發程序對受控程式碼的任何引用，都計入 32 層巢狀限制中的一層。 在受控代码中叫用的方法不計入此限制。
如果允許巢狀觸發程序，但鏈結中的觸發程序形成一個無限迴圈時，則觸發程序會因為超過巢狀層級而終止執行。
您可利用巢狀觸發程序來執行實用的內部管理功能，如儲存被前一個觸發程序所影響的資料列備份。 例如，您可在 PurchaseOrderDetail 上建立觸發程序，其可儲存經 PurchaseOrderDetail 觸發程序所刪除的 delcascadetrig 資料列備份。 在 delcascadetrig 觸發程序生效的情況下，從 PurchaseOrderHeader 刪除 PurchaseOrderID 1965，將也刪除 PurchaseOrderDetail 中對應的一或多個資料列。 若要儲存資料，您可在 PurchaseOrderDetail 上建立 DELETE 觸發程序，將刪除的資料儲存至另外建立的資料表 del_save。 例如：
CREATE TRIGGER Purchasing.savedel
ON Purchasing.PurchaseOrderDetail
FOR DELETE
AS
INSERT del_save
SELECT * FROM deleted;
我們不建議您在與順序有關的序列中使用巢狀觸發程序。 請使用個別的觸發條件來連鎖資料修改。
注意
因觸發程序是在交易內執行，所以在整組巢狀觸發程序中，只要有一層在執行時發生錯誤，即會取消整個交易，所有的修改資料均會回復原狀。 您可在觸發器中加入 PRINT 陳述式，這樣便可知道錯誤發生的位置。
遞迴觸發器
除非設定 RECURSIVE_TRIGGERS 資料庫選項，否則 AFTER 觸發程序不會以遞迴方式自我呼叫。
而遞迴有以下兩種不同類型：
直接遞迴
當觸發器啟動並執行導致相同觸發器再次啟動的動作時，就會發生遞迴。 例如，應用程式會更新資料表 T3；這將使觸發程序 Trig3 引發。 Trig3 會再次更新 T3 ；這將使觸發程序 Trig3 再度引發。
在呼叫不同類型的觸發程序 (AFTER 或 INSTEAD OF) 之後，再次呼叫相同的觸發程序時，也會發生直接遞迴。 換句話說，即使在第一次和第二次呼叫之間執行了一個或多個 AFTER 觸發程序時，如果第二次呼叫相同的 INSTEAD OF 觸發程序，仍然會發生該 INSTEAD OF 觸發程序的直接遞迴。 同樣地，即使在第一次與第二次呼叫之間呼叫了一或多個 INSTEAD OF 觸發程序，第二次呼叫相同 AFTER 觸發程序時，仍會發生 AFTER 觸發程序的直接遞迴。 例如，應用程式會更新資料表 T4。 這項更新將觸發 INSTEAD OF 觸發程序 Trig4 執行。 Trig4 會更新資料表 T5。 這項更新會引發後置觸發器 Trig5。 Trig5 會更新資料表 T4，而這項更新會再次觸發 INSTEAD OF 觸發程序 Trig4。 這個事件鏈結被視為 Trig4的直接遞迴。
間接遞迴
當某個觸發程序引發並執行一個動作，該動作又引發相同類型（AFTER 或 INSTEAD OF）的另一個觸發程序時，就會發生這類遞迴。 第二個觸發器會執行一個動作，使原始觸發器再次啟動。 換句話說，當另一個 INSTEAD OF 觸發程序在兩次呼叫之間被呼叫時，就會發生間接遞迴。 同樣地，間接遞迴會在當第一次呼叫 AFTER 觸發程序後，又在兩次呼叫之間呼叫另一個 AFTER 觸發程序時發生。 例如，應用程式會更新資料表 T1。 這項更新會引發 AFTER 觸發器 Trig1。 Trig1 會更新資料表 T2，而這項更新會引發 AFTER 觸發器 Trig2。 接著，Trig2 更新了資料表 T1，這導致 AFTER 觸發程序 Trig1 再次被觸發。
RECURSIVE_TRIGGERS 資料庫選項設定為 OFF 時，只能防止 AFTER 觸發程序的直接遞迴。 若要停用 AFTER 觸發程序的間接遞迴，也請將 巢狀觸發程序 伺服器選項設定為 0。
範例
下列範例顯示如何使用遞迴觸發程序來解決自我參考關聯 (又名為遞移封閉)。 例如， emp_mgr 資料表定義下列項目：
公司中的員工 (
emp)。每名員工的經理 (
mgr)。每位員工所屬的組織樹中向其匯報的員工總數 (
NoOfReports)。
當有新員工記錄插入時，遞迴 UPDATE 觸發程序可使 NoOfReports 資料行同時保持最新的資料。 INSERT 觸發程序在更新經理記錄的 NoOfReports 資料行時，遞迴更新管理階層中其他記錄的 NoOfReports 資料行。
USE AdventureWorks2022;
GO
-- Turn recursive triggers ON in the database.
ALTER DATABASE AdventureWorks2022
SET RECURSIVE_TRIGGERS ON;
GO
CREATE TABLE dbo.emp_mgr (
emp char(30) PRIMARY KEY,
mgr char(30) NULL FOREIGN KEY REFERENCES emp_mgr(emp),
NoOfReports int DEFAULT 0
);
GO
CREATE TRIGGER dbo.emp_mgrins ON dbo.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 dbo.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 dbo.emp_mgrupd ON dbo.emp_mgr FOR UPDATE
AS
IF UPDATE (mgr)
BEGIN
UPDATE dbo.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 dbo.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 dbo.emp_mgr(emp, mgr) VALUES
('Harry', NULL)
,('Alice', 'Harry')
,('Paul', 'Alice')
,('Joe', 'Alice')
,('Dave', 'Joe');
GO
SELECT emp,mgr,NoOfReports
FROM dbo.emp_mgr;
GO
-- Change Dave's manager from Joe to Harry
UPDATE dbo.emp_mgr SET mgr = 'Harry'
WHERE emp = 'Dave';
GO
SELECT emp,mgr,NoOfReports 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
若要設定巢狀觸發程序選項
若要設定 RECURSIVE_TRIGGERS 資料庫選項