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

DML トリガーのコードを記述するときは、トリガーを起動するステートメントが、1 行ではなく複数のデータ行に影響する単一のステートメントである可能性があることを考慮してください。 これらのステートメントは複数の行に頻繁に影響するため、UPDATE トリガーと DELETE トリガーではこの動作が一般的です。 基本的な INSERT ステートメントでは 1 行しか追加されないため、INSERT トリガーの動作はあまり一般的ではありません。 ただし、INSERT トリガーは INSERT INTO (table_name) SELECT ステートメントによって起動できるため、多数の行を挿入すると、1 つのトリガー呼び出しが発生する可能性があります。

複数行の考慮事項は、DML トリガーの関数が 1 つのテーブルから集計値を自動的に再計算し、継続的な集計のために結果を別のテーブルに格納する場合に特に重要です。

例示

次の例の DML トリガーは、 AdventureWorks2012 サンプル データベースの別のテーブルに列の実行合計を格納するように設計されています。

ある。 単一行挿入の累計を格納する

データ行が PurchaseOrderDetail テーブルに読み込まれる場合、DML トリガーの最初のバージョンは 1 行の挿入に適しています。 INSERT ステートメントによって DML トリガーが起動され、トリガーの実行期間中、 挿入 されたテーブルに新しい行が読み込まれます。 UPDATE ステートメントは、行のLineTotal列の値を読み取り、その値をPurchaseOrderHeaderテーブルのSubTotal列の既存の値に追加します。 WHERE句を使用すると、PurchaseOrderDetail テーブル内の更新された行が、挿入されたテーブル内の行のPurchaseOrderIDと一致します。

-- Trigger is valid for single-row inserts.  
USE AdventureWorks2012;  
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) 内の代入式の右側にある式は、値のリストではなく、1 つの値に限定できます。 したがって、トリガーの効果は、挿入されたテーブル内の任意の行から値を取得し、その値を特定のPurchaseOrderID値のPurchaseOrderHeader テーブル内の既存のSubTotal値に追加することです。 挿入されたテーブルで 1 つのPurchaseOrderID値が複数回発生した場合、この操作は予期される効果を持たない可能性があります。

PurchaseOrderHeader テーブルを正しく更新するには、トリガーで挿入されたテーブル内の複数の行が許可されている必要があります。 これを行うには、挿入されたテーブル内の各PurchaseOrderIDの行グループの合計LineTotalを計算するSUM関数を使用します。 SUM関数は、相関サブクエリ (かっこ内の SELECT ステートメント) に含まれています。 このサブクエリは、挿入されたテーブル内のPurchaseOrderIDごとに、PurchaseOrderHeader テーブル内のPurchaseOrderIDと一致または関連付けられた 1 つの値を返します。

-- Trigger is valid for multirow and single-row inserts.  
USE AdventureWorks2012;  
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. 挿入タイプに基づいて、ランニングトータルを格納する

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

-- Trigger valid for multirow and single row inserts  
-- and optimal for single row inserts.  
USE AdventureWorks2012;  
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 トリガー