入れ子になったトリガーの作成
適用対象: 
SQL Server Azure SQL Database Azure SQL Managed Instance
あるトリガーが別のトリガーを起動する操作を実行するときは、DML トリガーと DDL トリガーの両方が入れ子になります。 このような操作では、他のトリガーを順次開始できます。 DML トリガーと DDL トリガーは、32 レベルまで入れ子にできます。 nested triggers サーバー構成オプションにより、AFTER トリガーを入れ子にできるかどうかを制御できます。 INSTEAD OF トリガーは、このサーバー オプションの設定とは無関係に入れ子にできます。INSTEAD OF トリガーにできるのは DML トリガーだけです。
Note
Transact-SQL トリガーからマネージド コードへの参照は、32 レベルの入れ子制限のうちの 1 レベルとカウントします。 マネージド コード内から呼び出されたメソッドは、この制限としてはカウントされません。
トリガーを入れ子にできる場合に、トリガーのチェーンのどれかが無限ループを開始すると、入れ子階層の上限を超えることになり、トリガーは終了します。
入れ子になったトリガーを使用して、前のトリガーの影響を受けた行のバックアップ コピーを保存するなど、システムの運用上有益な機能を実行することができます。 たとえば、 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;
入れ子の順序に依存するトリガーを使用することはお勧めしません。 個別のトリガーを使用し、順番にデータ修正を行ってください。
Note
トリガーはトランザクション内で実行されるので、入れ子になったトリガーのいずれかのレベルで障害が発生すると、トランザクション全体が取り消され、すべてのデータ修正がロールバックされます。 どこで障害が発生したかを判断できるように、トリガーに PRINT ステートメントを含めてください。
再帰トリガー
RECURSIVE_TRIGGERS データベース オプションが ON になっている場合を除いて、AFTER トリガーが自分自身を再帰呼び出しすることはありません。
再帰には、次の 2 種類があります。
直接再帰
起動されたトリガーによる処理が、同じトリガーを再び起動する場合にこの再帰が発生します。 たとえば、アプリケーションで T3テーブルが更新され、これにより Trig3 トリガーが起動されたとします。 Trig3 がテーブル T3 を更新するトリガーだとすると、テーブルが再度更新され、 Trig3 が再び起動されることになります。
別の種類 (AFTER または INSTEAD OF) のトリガーが呼び出された後で、同じトリガーが呼び出されても、直接再帰が発生します。 つまり、同じ INSTEAD OF トリガーが 2 回呼び出されると、その間に AFTER トリガーが 1 回以上呼び出されていたとしても、INSTEAD OF トリガーの直接再帰が発生します。 同様に、同じ AFTER トリガーが 2 回呼び出されると、その間に INSTEAD OF トリガーが 1 回以上呼び出されていたとしても、AFTER トリガーの直接再帰が発生します。 たとえば、アプリケーションがテーブル T4を更新します。 この更新により、INSTEAD OF トリガー Trig4 が起動します。 Trig4 はテーブル T5を更新します。 この更新により、AFTER トリガー Trig5 が起動します。 Trig5 がテーブル T4を更新し、これにより INSTEAD OF トリガー Trig4 が再び起動されます。 このようなイベントの連鎖は、 Trig4に対する直接再帰と見なされます。
間接再帰
起動されたトリガーが実行した処理によって、同じ種類 (AFTER または INSTEAD OF) の別のトリガーが起動する場合、この再帰が発生します。 この 2 番目のトリガーにより、最初のトリガーを再度起動する操作が実行されます。 つまり、ある INSTEAD OF トリガーが 2 回呼び出され、その間に別の INSTEAD OF トリガーが呼び出されていると、間接再帰が発生します。 同様に、ある AFTER トリガーが 2 回呼び出され、その間に別の AFTER トリガーが呼び出されていると、間接再帰が発生します。 たとえば、アプリケーションがテーブル T1を更新します。 この更新により、AFTER トリガー Trig1 が起動します。 Trig1 がテーブル T2を更新し、これにより AFTER トリガー Trig2 が起動します。 次に、Trig2 がテーブル T1 を更新し、これにより AFTER トリガー Trig1 が再び起動します。
RECURSIVE_TRIGGERS データベース オプションが OFF の場合は、AFTER トリガーの直接再帰呼び出しのみが回避されます。 AFTER トリガーの間接再帰を無効にするには、 nested triggers サーバー オプションを 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 データベース オプションを設定するには
参照
CREATE TRIGGER (Transact-SQL)
nested triggers サーバー構成オプションの構成