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

更新 : 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 の参考資料の入手

変更履歴

リリース	履歴
2005 年 12 月 5 日	変更内容 : 直接トリガ再帰および間接トリガ再帰を定義する新しい規則を説明するため、「再帰トリガ」の情報を変更しました。