トリガーが別のトリガーを開始するアクションを実行すると、DML トリガーと DDL トリガーの両方が入れ子になります。 これらのアクションは、他のトリガーを開始できます。 DML トリガーと DDL トリガーは、最大 32 レベルまで入れ子にすることができます。 ネストされたトリガーのサーバー構成オプションを使用して、AFTERトリガーをネストできるかどうかを制御できます。 この設定に関係なく、INSTEAD OF トリガー (INSTEAD OF トリガーになれるのは DML トリガーのみです) は入れ子にできます。
注
Transact-SQL トリガーからのマネージド コードへの参照は、32 レベルの入れ子制限に対して 1 つのレベルとしてカウントされます。 マネージド コード内から呼び出されたメソッドは、この制限に対してカウントされません。
入れ子になったトリガーが許可され、チェーン内のトリガーが無限ループを開始すると、入れ子レベルを超え、トリガーが終了します。
入れ子になったトリガーを使用して、前のトリガーの影響を受けた行のバックアップ コピーを格納するなど、便利なハウスキーピング機能を実行できます。 たとえば、delcascadetrig トリガーが削除したPurchaseOrderDetail行のバックアップ コピーを保存するトリガーをPurchaseOrderDetailに作成できます。
delcascadetrig トリガーが有効な状態で、PurchaseOrderHeaderから PurchaseOrderID 1965 を削除すると、対応する行がPurchaseOrderDetailから削除されます。 データを保存するには、削除されたデータを別の個別に作成されたテーブル (del_save) に保存する DELETE トリガーをPurchaseOrderDetailに作成します。 例えば次が挙げられます。
CREATE TRIGGER Purchasing.savedel
ON Purchasing.PurchaseOrderDetail
FOR DELETE
AS
INSERT del_save;
SELECT * FROM deleted;
順序に依存するシーケンスで入れ子になったトリガーを使用することはお勧めしません。 個別のトリガーを使用して、データ変更をカスケードします。
注
トリガーはトランザクション内で実行されるため、入れ子になったトリガーのセットの任意のレベルでエラーが発生すると、トランザクション全体が取り消され、すべてのデータ変更がロールバックされます。 エラーが発生した場所を特定できるように、トリガーに PRINT ステートメントを含めます。
再帰トリガー
after トリガーは、RECURSIVE_TRIGGERS データベース オプションが設定されていない限り、それ自体を再帰的に呼び出しません。
再帰には次の 2 種類があります。
直接再帰
この再帰は、トリガーが起動し、同じトリガーが再度起動するアクションを実行するときに発生します。 たとえば、アプリケーションはテーブル T3 を更新します。これによりトリガー Trig3 が起動します。 Trig3 はテーブル T3 をもう一度更新します。これにより、トリガー Trig3 が再び起動します。
直接再帰は、同じトリガーが再度呼び出されたが、別の型 (AFTER または INSTEAD OF) のトリガーが呼び出された後にも発生する可能性があります。 つまり、間に 1 つ以上の AFTER トリガーが呼び出されたとしても、同じ INSTEAD OF トリガーが 2 回目に呼び出されたときに、INSTEAD OF トリガーの直接再帰が発生する可能性があります。 同様に、AFTER トリガーの直接再帰は、途中で 1 つ以上の INSTEAD OF トリガーが呼び出された場合でも、同じ 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 では、AFTER トリガー Trig1 が再び起動するテーブル T1 が更新されます。
RECURSIVE_TRIGGERS データベース オプションが OFF に設定されている場合は、AFTER トリガーの直接再帰のみが防止されます。 AFTER トリガーの間接再帰を無効にするには、 入れ子になったトリガー サーバー オプションも 0 に設定します。
例示
次の例は、再帰トリガーを使用して自己参照関係 (推移的なクロージャとも呼ばれます) を解決する方法を示しています。 たとえば、テーブル emp_mgr は次のように定義します。
会社の従業員 (
emp)。各従業員のマネージャー (
mgr)。各従業員 (
NoOfReports) にレポートする組織ツリー内の従業員の合計数。
再帰 UPDATE トリガーを使用すると、新しい従業員レコードが挿入されると、 NoOfReports 列 up-to-date を保持できます。 INSERT トリガーは、マネージャー レコードの NoOfReports 列を更新します。この列は、管理階層の他のレコードの NoOfReports 列を再帰的に更新します。
USE AdventureWorks2012;
GO
-- Turn recursive triggers ON in the database.
ALTER DATABASE AdventureWorks2012
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 データベース オプションを設定するには