建立巢狀觸發程序

發行項
05/04/2023

適用於：SQL Server Azure SQL 資料庫 Azure SQL 受控執行個體

觸發程序執行用來起始另一個觸發程序的動作時，DML 和 DDL 觸發程序就是巢狀觸發程序。這些動作可以起始化其他觸發程序等等。 DML 與 DDL 觸發程序最多可以巢狀方式嵌套多達 32 層。您可以透過 [巢狀觸發程序] 伺服器組態選項來控制 AFTER 觸發程序是否可為巢狀。不論此設定為何，INSTEAD OF 觸發程序 (只有 DML 觸發程序可為 INSTEAD OF 觸發程序) 均可為巢狀。

注意

任何從 Transact-SQL 觸發程序對受控程式碼的參考，都會算成 32 層巢狀限制中的一層。從 Managed 程式碼內叫用的方法，不列入這項限制。

如果允許巢狀觸發程序，但鏈結中的觸發程序形成一個無限迴圈時，則觸發程序會因為超過巢狀層級而終止執行。

您可利用巢狀觸發程序來執行實用的內部管理功能，如儲存被前一個觸發程序所影響的資料列備份。例如，您可在 PurchaseOrderDetail 上建立觸發程序，其可儲存經 PurchaseOrderDetail 觸發程序所刪除的 delcascadetrig 資料列備份。由於 delcascadetrig 觸發程序為作用中，刪除 PurchaseOrderID 的 PurchaseOrderHeader 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。這項更新會引發 AFTER 觸發程序 Trig5 。 Trig5 會更新資料表 T4，而這項更新會再次引發 INSTEAD OF 觸發程序 Trig4 。這個事件鏈結被視為 Trig4的直接遞迴。
間接遞迴

觸發程序引發並執行會引發另一個相同類型之觸發程序 (AFTER 或 INSTEAD OF) 的動作時，會發生這類遞迴。此第二個觸發程序執行使原始觸發程序再次引發的動作。換句話說，如果第二次呼叫 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

若要設定巢狀觸發程序選項

sp_configure (Transact-SQL)

若要設定 RECURSIVE_TRIGGERS 資料庫選項

ALTER DATABASE SET 選項 (Transact-SQL)

另請參閱

CREATE TRIGGER (Transact-SQL)
設定 nested triggers 伺服器組態選項

共用方式為

建立巢狀觸發程序

遞迴觸發程序

範例

另請參閱

意見反應

其他資源