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

適用対象:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceMicrosoft Fabric の SQL データベース

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

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

例

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

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

最初の例の DML トリガーは、 PurchaseOrderDetail テーブルに 1 行のデータが追加される場合、つまり 1 行単位の挿入は問題なく実行できます。 INSERT文がDMLトリガーを発動し、新しい行はトリガー実行の間挿入されたテーブルにロードされます。 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 つの PurchaseOrderID が inserted テーブルに複数登録されている場合には、予想どおりの結果が得られないことがあります。

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 つの行だけの値になります。 ただし、このトリガーでは、相関サブクエリと IN 句で使用されている WHERE演算子は、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 トリガー