複数行のデータを処理するための DML トリガーの作成

適用対象: SQL Server Azure SQL Database Azure SQL Managed Instance

DML トリガーのコードを記述するときは、トリガーを起動するステートメントが、1 行だけではなく複数行のデータに影響する場合があることを考慮してください。 この動作は UPDATE トリガーや DELETE トリガーの場合によく見られます。UPDATE ステートメントや DELETE ステートメントが複数行に影響を与えることが多いためです。 INSERT トリガーの場合は、INSERT ステートメントが 1 行単位で追加を行うため、この動作はあまり見られません。 ただし、INSERT トリガーは INSERT INTO (table_name) SELECT ステートメントにより起動されることもあります。その場合は、1 回のトリガーの呼び出しで複数行が挿入される可能性があります。

複数行への影響について考慮することは、DML トリガーの機能によって 1 つのテーブルから自動的に集計値が再計算され、その結果を他のテーブルに保存して集計を続行するような場合は、特に重要です。

Note

パフォーマンスが低下する可能性があるので、トリガーにカーソルを使用することはお勧めしません。 複数行に影響を与えるトリガーを設計する場合は、カーソルではなく行セットをベースにしたロジックを使用してください。

次の例の DML トリガーはどれも、ある列の集計の途中経過を AdventureWorks2022 サンプル データベースの別のテーブル内に保存するように設計されています。

A. 単一の行を挿入する場合の集計途中経過の格納

最初の例の DML トリガーは、 PurchaseOrderDetail テーブルに 1 行のデータが追加される場合、つまり 1 行単位の挿入は問題なく実行できます。 INSERT ステートメントにより DML トリガーが起動され、トリガーの実行中に inserted テーブルに新しい行が追加されます。 UPDATE ステートメントでは行の LineTotal 列の値が読み取られ、その値が SubTotal テーブルの PurchaseOrderHeader 列の既存の値に加算されます。 WHERE 句により、 PurchaseOrderDetail テーブルで更新された行が PurchaseOrderID inserted テーブルの行の に一致することが保証されます。

-- Trigger is valid for single-row inserts.  
USE AdventureWorks2022;  
GO  
CREATE TRIGGER NewPODetail  
ON Purchasing.PurchaseOrderDetail  
AFTER INSERT AS  
   UPDATE PurchaseOrderHeader  
   SET SubTotal = SubTotal + LineTotal  
   FROM inserted  
   WHERE PurchaseOrderHeader.PurchaseOrderID = inserted.PurchaseOrderID ;  

B. 複数または単一の行を挿入する場合の集計途中経過の格納

複数行を挿入する場合は、例 A で示した DML トリガーは正しく実行されない可能性があります。UPDATE ステートメントの代入式の右側の式 (SubTotal + LineTotal) は必ず単一の値になり、値のリストを指定することはできません。 したがって、このトリガーの結果は、 inserted テーブル内の 1 行から値を取得して、その値を、特定の SubTotal 値に対する PurchaseOrderHeader テーブルの既存の PurchaseOrderID 値に追加することになります。 この操作では、1 つの PurchaseOrderIDinserted テーブルに複数登録されている場合には、予想どおりの結果が得られないことがあります。

PurchaseOrderHeader テーブルを正しく更新するには、 inserted テーブルに複数の行がある場合を考慮してトリガーを作成する必要があります。 これを行うには、 SUM ごとに LineTotal inserted テーブル内の行グループで の合計を計算する PurchaseOrderID関数を使用します。 SUM 関数は、相関サブクエリ (かっこ内の SELECT ステートメント) に指定します。 このサブクエリは、 PurchaseOrderID テーブルの に一致するか、これと相関関係にある inserted PurchaseOrderID テーブルの PurchaseOrderHeader の各値を返します。

-- Trigger is valid for multirow and single-row inserts.  
USE AdventureWorks2022;  
GO  
CREATE TRIGGER NewPODetail2  
ON Purchasing.PurchaseOrderDetail  
AFTER INSERT AS  
   UPDATE Purchasing.PurchaseOrderHeader  
   SET SubTotal = SubTotal +   
      (SELECT SUM(LineTotal)  
      FROM inserted  
      WHERE PurchaseOrderHeader.PurchaseOrderID  
       = inserted.PurchaseOrderID)  
   WHERE PurchaseOrderHeader.PurchaseOrderID IN  
      (SELECT PurchaseOrderID FROM inserted);  

このトリガーは 1 行だけの挿入でも正しく実行されます。この場合、 LineTotal 値の列の合計は、1 つの行だけの値になります。 ただし、このトリガーでは、相関サブクエリと WHERE 句で使用されている IN演算子は、SQL Serverによる追加の処理を必要とします。 これは、1 行単位の挿入操作の場合は必要ありません。

C: 挿入の種類に基づいた集計の途中経過の格納

処理対象の行数に応じて最適の方法を使用するようにトリガーを変更できます。 たとえば、1 行だけの挿入と複数行の挿入を区別するには、トリガーのロジックで @@ROWCOUNT 関数を使用します。

-- Trigger valid for multirow and single row inserts  
-- and optimal for single row inserts.  
USE AdventureWorks2022;  
GO  
CREATE TRIGGER NewPODetail3  
ON Purchasing.PurchaseOrderDetail  
FOR INSERT AS  
IF @@ROWCOUNT = 1  
BEGIN  
   UPDATE Purchasing.PurchaseOrderHeader  
   SET SubTotal = SubTotal + LineTotal  
   FROM inserted  
   WHERE PurchaseOrderHeader.PurchaseOrderID = inserted.PurchaseOrderID  
END  
ELSE  
BEGIN  
      UPDATE Purchasing.PurchaseOrderHeader  
   SET SubTotal = SubTotal +   
      (SELECT SUM(LineTotal)  
      FROM inserted  
      WHERE PurchaseOrderHeader.PurchaseOrderID  
       = inserted.PurchaseOrderID)  
   WHERE PurchaseOrderHeader.PurchaseOrderID IN  
      (SELECT PurchaseOrderID FROM inserted)  
END;  

参照

DML トリガー