入れ子になったトリガの使用
あるトリガが別のトリガを起動する操作を実行するときは、DML トリガと DDL トリガの両方が入れ子になります。このような操作では、他のトリガを順次開始できます。DML トリガと DDL トリガは、32 レベルまで入れ子にできます。Transact-SQL トリガからマネージ コードへの参照は、32 レベルの入れ子制限の 1 レベルとしてカウントされます。マネージ コード内から呼び出されたメソッドは、この制限としてはカウントされません。
nested triggers サーバー構成オプションにより、AFTER トリガを入れ子にできるかどうかを制御できます。INSTEAD OF トリガは、このサーバー オプションの設定とは無関係に入れ子にできます。INSTEAD OF トリガにできるのは DML トリガだけです。
.gif "注意") 注意 |
|---|
SQL Server 2000 では、INSTEAD OF トリガ内で入れ子になった AFTER トリガは、nested triggers サーバー構成オプションがオフになっていれば起動されません。SQL Server 2005 以降では、INSTEAD OF トリガ内で入れ子になっている最初の AFTER トリガは、nested triggers サーバー構成オプションが 0 に設定されていても起動されます。ただし、この設定では、後続の AFTER トリガは起動されません。アプリケーションに入れ子になったトリガがないかどうかを調査し、nested triggers サーバー構成オプションが 0 に設定されている場合の動作に関して、アプリケーションがビジネス ルールに従っているかどうかを判断し、その後、適切な変更を加えることをお勧めします。 |
トリガを入れ子にできる場合に、トリガのチェーンのどれかが無限ループを開始すると、入れ子階層の上限を超えることになり、トリガは終了します。
入れ子になったトリガを使用して、前のトリガの影響を受けた行のバックアップ コピーを保存するなど、システムの運用上有益な機能を実行することができます。たとえば、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 2008 では、別の種類 (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
入れ子になったトリガのオプションを設定するには
RECURSIVE_TRIGGERS データベース オプションを設定するには