入れ子になったトリガーの作成Create Nested Triggers

適用対象: ○SQL Server (2008 以降) ○Azure SQL Database XAzure SQL Data Warehouse XParallel Data Warehouse APPLIES TO: yesSQL Server (starting with 2008) yesAzure SQL Database noAzure SQL Data Warehouse noParallel Data Warehouse

あるトリガーが別のトリガーを起動する操作を実行するときは、DML トリガーと DDL トリガーの両方が入れ子になります。Both DML and DDL triggers are nested when a trigger performs an action that initiates another trigger. このような操作では、他のトリガーを順次開始できます。These actions can initiate other triggers, and so on. DML トリガーと DDL トリガーは、32 レベルまで入れ子にできます。DML and DDL triggers can be nested up to 32 levels. nested triggers サーバー構成オプションにより、AFTER トリガーを入れ子にできるかどうかを制御できます。You can control whether AFTER triggers can be nested through the nested triggers server configuration option. INSTEAD OF トリガーは、このサーバー オプションの設定とは無関係に入れ子にできます。INSTEAD OF トリガーにできるのは DML トリガーだけです。INSTEAD OF triggers (only DML triggers can be INSTEAD OF triggers) can be nested regardless of this setting.

注意

Transact-SQLTransact-SQL トリガーからマネージド コードへの参照は、32 レベルの入れ子制限の 1 レベルとしてカウントされます。Any reference to managed code from a Transact-SQLTransact-SQL trigger counts as one level against the 32-level nesting limit. マネージド コード内から呼び出されたメソッドは、この制限としてはカウントされません。Methods invoked from within managed code do not count against this limit.

トリガーを入れ子にできる場合に、トリガーのチェーンのどれかが無限ループを開始すると、入れ子階層の上限を超えることになり、トリガーは終了します。If nested triggers are allowed and a trigger in the chain starts an infinite loop, the nesting level is exceeded and the trigger terminates.

入れ子になったトリガーを使用して、前のトリガーの影響を受けた行のバックアップ コピーを保存するなど、システムの運用上有益な機能を実行することができます。You can use nested triggers to perform useful housekeeping functions such as storing a backup copy of rows affected by a previous trigger. たとえば、 PurchaseOrderDetail トリガーが削除した PurchaseOrderDetail 行のバックアップ コピーを保存するトリガーを delcascadetrig に作成することができます。For example, you can create a trigger on PurchaseOrderDetail that saves a backup copy of the PurchaseOrderDetail rows that the delcascadetrig trigger deleted. delcascadetrig トリガーが有効な場合、 PurchaseOrderID から PurchaseOrderHeader 1965 が削除されると、 PurchaseOrderDetailから対応する行が削除されます。With the delcascadetrig trigger in effect, deleting PurchaseOrderID 1965 from PurchaseOrderHeader deletes the corresponding row or rows from PurchaseOrderDetail. このデータを保存するには、 PurchaseOrderDetail に DELETE トリガーを作成します。このトリガーでは削除されたデータが、別に作成されたテーブル del_saveに保存されます。To save the data, you can create a DELETE trigger on PurchaseOrderDetail that saves the deleted data into another separately created table, del_save. 例 :For example:

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

入れ子の順序に依存するトリガーを使用することはお勧めしません。We do not recommend using nested triggers in an order-dependent sequence. 個別のトリガーを使用し、順番にデータ修正を行ってください。Use separate triggers to cascade data modifications.

注意

トリガーはトランザクション内で実行されるので、入れ子になったトリガーのいずれかのレベルで障害が発生すると、トランザクション全体が取り消され、すべてのデータ修正がロールバックされます。Because triggers execute within a transaction, a failure at any level of a set of nested triggers cancels the entire transaction, and all data modifications are rolled back. どこで障害が発生したかを判断できるように、トリガーに PRINT ステートメントを含めてください。Include PRINT statements in your triggers so that you can determine where the failure has occurred.

再帰トリガーRecursive Triggers

RECURSIVE_TRIGGERS データベース オプションが ON になっている場合を除いて、AFTER トリガーが自分自身を再帰呼び出しすることはありません。An AFTER trigger does not call itself recursively unless the RECURSIVE_TRIGGERS database option is set.

再帰には、次の 2 種類があります。There are two types of recursion:

  • 直接再帰Direct recursion

    起動されたトリガーによる処理が、同じトリガーを再び起動する場合にこの再帰が発生します。This recursion occurs when a trigger fires and performs an action that causes the same trigger to fire again. たとえば、アプリケーションで T3テーブルが更新され、これにより Trig3 トリガーが起動されたとします。For example, an application updates table T3; this causes trigger Trig3 to fire. Trig3 がテーブル T3 を更新するトリガーだとすると、テーブルが再度更新され、 Trig3 が再び起動されることになります。Trig3 updates table T3 again; this causes trigger Trig3 to fire again.

    別の種類 (AFTER または INSTEAD OF) のトリガーが呼び出された後で、同じトリガーが呼び出されても、直接再帰が発生します。Direct recursion can also occur when the same trigger is called again, but after a trigger of a different type (AFTER or INSTEAD OF) is called. つまり、同じ INSTEAD OF トリガーが 2 回呼び出されると、その間に AFTER トリガーが 1 回以上呼び出されていたとしても、INSTEAD OF トリガーの直接再帰が発生します。In other words, direct recursion of an INSTEAD OF trigger can occur when the same INSTEAD OF trigger is called for a second time, even if one or more AFTER triggers are called in between. 同様に、同じ AFTER トリガーが 2 回呼び出されると、その間に INSTEAD OF トリガーが 1 回以上呼び出されていたとしても、AFTER トリガーの直接再帰が発生します。Likewise, direct recursion of an AFTER trigger can occur when the same AFTER trigger is called for a second time, even if one or more INSTEAD OF triggers are called in between. たとえば、アプリケーションがテーブル T4を更新します。For example, an application updates table T4. この更新により、INSTEAD OF トリガー Trig4 が起動します。This update causes INSTEAD OF trigger Trig4 to fire. Trig4 はテーブル T5を更新します。Trig4 updates table T5. この更新により、AFTER トリガー Trig5 が起動します。This update causes AFTER trigger Trig5 to fire. Trig5 がテーブル T4を更新し、これにより INSTEAD OF トリガー Trig4 が再び起動されます。Trig5 updates table T4, and this update causes INSTEAD OF trigger Trig4 to fire again. このようなイベントの連鎖は、 Trig4に対する直接再帰と見なされます。This chain of events is considered direct recursion for Trig4.

  • 間接再帰Indirect recursion

    起動されたトリガーが実行した処理によって、同じ種類 (AFTER または INSTEAD OF) の別のトリガーが起動する場合、この再帰が発生します。This recursion occurs when a trigger fires and performs an action that causes another trigger of the same type (AFTER or INSTEAD OF) to fire. この 2 番目のトリガーにより、最初のトリガーを再度起動する操作が実行されます。This second trigger performs an action that causes the original trigger to fire again. つまり、ある INSTEAD OF トリガーが 2 回呼び出され、その間に別の INSTEAD OF トリガーが呼び出されていると、間接再帰が発生します。In other words, indirect recursion can occur when an INSTEAD OF trigger is called for a second time, but not until another INSTEAD OF trigger is called in between. 同様に、ある AFTER トリガーが 2 回呼び出され、その間に別の AFTER トリガーが呼び出されていると、間接再帰が発生します。Likewise, indirect recursion can occur when an AFTER trigger is called for a second time, but not until another AFTER trigger is called in between. たとえば、アプリケーションがテーブル T1を更新します。For example, an application updates table T1. この更新により、AFTER トリガー Trig1 が起動します。This update causes AFTER trigger Trig1 to fire. Trig1 がテーブル T2を更新し、これにより AFTER トリガー Trig2 が起動します。Trig1 updates table T2, and this update causes AFTER trigger Trig2 to fire. 次に、Trig2 がテーブル T1 を更新し、これにより AFTER トリガー Trig1 が再び起動します。Trig2 in turn updates table T1 that causes AFTER trigger Trig1 to fire again.

RECURSIVE_TRIGGERS データベース オプションが OFF の場合は、AFTER トリガーの直接再帰呼び出しのみが回避されます。Only direct recursion of AFTER triggers is prevented when the RECURSIVE_TRIGGERS database option is set to OFF. AFTER トリガーの間接再帰を無効にするには、 nested triggers サーバー オプションを 0に設定します。To disable indirect recursion of AFTER triggers, also set the nested triggers server option to 0.

使用例Examples

次の例では、再帰トリガーを使用して、自己参照型リレーションシップ (トランジティブ クロージャとも呼ばれます) を解決する方法を示しています。The following example shows using recursive triggers to solve a self-referencing relationship (also known as transitive closure). たとえば、 emp_mgr テーブルで、次のものが定義されているとします。For example, the table emp_mgr defines the following:

  • 会社内の従業員 (emp)An employee (emp) in a company.

  • 各従業員の管理者 (mgr)The manager for each employee (mgr).

  • 各従業員へ報告を行う、組織構成内の従業員の総数 (NoOfReports)The total number of employees in the organizational tree reporting to each employee (NoOfReports).

再帰的な UPDATE トリガーを使用すると、新しい従業員のレコードが挿入されたときに NoOfReports 列を最新の状態に更新できます。A recursive UPDATE trigger can be used to keep the NoOfReports column up-to-date as new employee records are inserted. INSERT トリガーにより、その従業員の管理者のレコードの NoOfReports 列の値が更新されます。これにより組織構成の上部に向かって、その他のレコードの NoOfReports 列が再帰的に更新されます。The INSERT trigger updates the NoOfReports column of the manager record, which recursively updates the NoOfReports column of other records up the management hierarchy.

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  

以下は、更新を行う前の状態です。Here are the results before the update.

emp                            mgr                           NoOfReports  
------------------------------ ----------------------------- -----------  
Alice                          Harry                          2  
Dave                           Joe                            0  
Harry                          NULL                           1  
Joe                            Alice                          1  
Paul                           Alice                          0  

以下は、更新を行った後の状態です。Here are the results after the update.

emp                            mgr                           NoOfReports  
------------------------------ ----------------------------- -----------  
Alice                          Harry                          2  
Dave                           Harry                          0  
Harry                          NULL                           2  
Joe                            Alice                          0  
Paul                           Alice                          0  

入れ子になったトリガーのオプションを設定するにはTo set the nested triggers option

RECURSIVE_TRIGGERS データベース オプションを設定するにはTo set the RECURSIVE_TRIGGERS database option

参照See Also

CREATE TRIGGER (Transact-SQL) CREATE TRIGGER (Transact-SQL)
nested triggers サーバー構成オプションの構成Configure the nested triggers Server Configuration Option