入れ子になったトリガの使用

更新 : 2005 年 12 月 5 日

あるトリガが別のトリガを起動する操作を実行するときは、DML トリガと DDL トリガの両方が入れ子になります。このような操作では、他のトリガを順次開始できます。DML トリガと DDL トリガは、32 レベルまで入れ子にできます。nested triggers サーバー構成オプションにより、AFTER トリガを入れ子にできるかどうかを制御できます。INSTEAD OF トリガは、このサーバー オプションの設定とは無関係に入れ子にできます。INSTEAD OF トリガにできるのは DML トリガだけです。

メモ :
Transact-SQL トリガからマネージ コードへの参照は、32 レベルの入れ子制限の 1 レベルとしてカウントされます。マネージ コード内で呼び出されるメソッドについては、この制限としてカウントされません。

トリガを入れ子にできる場合に、トリガのチェーンのどれかが無限ループを開始すると、入れ子階層の上限を超えることになり、トリガは終了します。

入れ子になったトリガを使用して、前のトリガの影響を受けた行のバックアップ コピーを保存するなど、システムの運用上有益な機能を実行することができます。たとえば、delcascadetrig トリガが削除した PurchaseOrderDetail 行のバックアップ コピーを保存するトリガを PurchaseOrderDetail に作成することができます。delcascadetrig トリガが有効な場合、PurchaseOrderHeader から PurchaseOrderID 1965 が削除されると、PurchaseOrderDetail から対応する行が削除されます。このデータを保存するには、PurchaseOrderDetail に DELETE トリガを作成します。このトリガでは削除されたデータが、別に作成されたテーブル del_save に保存されます。次に例を示します。

CREATE TRIGGER savedel
   ON Purchasing.PurchaseOrderDetail
FOR DELETE
AS
   INSERT del_save
   SELECT * FROM deleted

入れ子の順序に依存するトリガを使用することはお勧めしません。個別のトリガを使用し、順番にデータ修正を行ってください。

メモ :
トリガはトランザクション内で実行されるので、入れ子になったトリガのいずれかのレベルで障害が発生すると、トランザクション全体が取り消され、すべてのデータ修正がロールバックされます。どこで障害が発生したかを判断できるように、トリガに PRINT ステートメントを含めてください。

再帰トリガ

RECURSIVE_TRIGGERS データベース オプションが ON になっている場合を除いて、AFTER トリガが自分自身を再帰呼び出しすることはありません。

再帰には、次の 2 種類があります。

• 直接再帰
  起動されたトリガによる処理が、同じトリガを再び起動する場合にこの再帰が発生します。たとえば、アプリケーションで T3 テーブルが更新され、これにより Trig3 トリガが起動されたとします。Trig3 がテーブル T3 を更新するトリガだとすると、テーブルが再度更新され、Trig3 が再び起動されることになります。
  SQL Server 2005 では、別の種類 (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 AdventureWorks;
GO
-- Turn recursive triggers ON in the database.
ALTER DATABASE AdventureWorks
   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

入れ子になったトリガのオプションを設定するには

• sp_configure (Transact-SQL)

RECURSIVE_TRIGGERS データベース オプションを設定するには

• ALTER DATABASE (Transact-SQL)

参照

概念

nested triggers オプション
DML トリガの実行
データベース オプションの設定

その他の技術情報

CREATE TRIGGER (Transact-SQL)

ヘルプおよび情報

SQL Server 2005 の参考資料の入手

変更履歴