Erstellen von DML-Triggern für die Verarbeitung mehrerer Datenzeilen

Wenn Sie den Code für einen DML-Trigger schreiben, sollten Sie berücksichtigen, dass die Anweisung, die bewirkt, dass der Auslöser ausgelöst wird, eine einzelne Anweisung sein kann, die sich auf mehrere Datenzeilen anstelle einer einzelnen Zeile auswirkt. Dieses Verhalten ist für UPDATE- und DELETE-Trigger üblich, da sich diese Anweisungen häufig auf mehrere Zeilen auswirken. Das Verhalten ist bei INSERT-Triggern weniger häufig, da die einfache INSERT-Anweisung nur eine einzelne Zeile hinzufügt. Da jedoch ein INSERT-Trigger durch eine INSERT INTO (table_name) SELECT-Anweisung ausgelöst werden kann, kann das Einfügen vieler Zeilen zu einem einzelnen Triggeraufruf führen.

Überlegungen zu mehreren Zeilen sind besonders wichtig, wenn die Funktion eines DML-Triggers die automatische Neuberechnung von Summenwerten aus einer Tabelle und das Speichern der Ergebnisse in einer anderen für fortlaufende Berechnungen ist.

Beispiele

Die DML-Trigger in den folgenden Beispielen dienen zum Speichern einer ausgeführten Gesamtsumme einer Spalte in einer anderen Tabelle der AdventureWorks2012-Beispieldatenbank .

Ein. Speichern einer laufenden Summe für einen Einzeilen-Insert

Die erste Version des DML-Triggers funktioniert gut für einen einzeiligen Einfügevorgang, wenn eine Datenzeile in die PurchaseOrderDetail Tabelle geladen wird. Eine INSERT-Anweisung löst den DML-Trigger aus, und die neue Zeile wird für die Dauer der Triggerausführung in die eingefügte Tabelle geladen. Die UPDATE Anweisung liest den LineTotal Spaltenwert für die Zeile und fügt diesen Wert dem vorhandenen Wert in der Spalte in der SubTotalPurchaseOrderHeader Tabelle hinzu. Die WHERE Klausel stellt sicher, dass die aktualisierte Zeile in der PurchaseOrderDetail Tabelle mit PurchaseOrderID der Zeile in der eingefügten Tabelle übereinstimmt.

-- 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. Speichern einer laufenden Summe für eine Mehrzeilen- oder Einzeilen-Einfügung

Bei einem Mehrrow-Einfügen funktioniert der DML-Auslöser in Beispiel A möglicherweise nicht ordnungsgemäß; Der Ausdruck rechts neben einem Zuordnungsausdruck in einer UPDATE-Anweisung (SubTotal + LineTotal) kann nur ein einzelner Wert und keine Liste von Werten sein. Daher besteht die Auswirkung des Triggers darin, einen Wert aus einer beliebigen einzelnen Zeile in der eingefügten Tabelle abzurufen und diesen Wert dem vorhandenen SubTotal Wert in der PurchaseOrderHeader Tabelle für einen bestimmten PurchaseOrderID Wert hinzuzufügen. Dieser Vorgang hat möglicherweise nicht den erwarteten Effekt, wenn ein einzelner PurchaseOrderID Wert mehr als einmal in der eingefügten Tabelle aufgetreten ist.

Um die PurchaseOrderHeader Tabelle ordnungsgemäß zu aktualisieren, muss der Trigger die Wahrscheinlichkeit mehrerer Zeilen in der eingefügten Tabelle zulassen. Dazu können Sie die SUM Funktion verwenden, die die Summe LineTotal für eine Gruppe von Zeilen in der eingefügten Tabelle für jede PurchaseOrderIDberechnet. Die SUM Funktion ist in einer korrelierten Unterabfrage enthalten (die SELECT Anweisung in Klammern). Diese Unterabfrage gibt einen einzelnen Wert für jede PurchaseOrderID in der Insert-Tabelle zurück, die mit einer PurchaseOrderID in der PurchaseOrderHeader Tabelle übereinstimmt oder in Beziehung steht.

-- 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);  

Dieser Auslöser funktioniert auch ordnungsgemäß bei einer einzelnen Zeileneinfügung; die Summe der LineTotal Wertspalte ist die Summe einer einzelnen Zeile. Jedoch erfordert dies, dass die korrelierten Unterabfragen und der Operator, der im WHERE verwendet wird, von SQL Server zusätzlich verarbeitet werden. Dies ist für eine Einfügeoperation für eine einzelne Zeile nicht erforderlich.

C. Speichern einer laufenden Summe basierend auf dem Typ des Einfügens

Sie können den Trigger ändern, um die Methode optimal für die Anzahl der Zeilen zu verwenden. Beispielsweise kann die @@ROWCOUNT Funktion in der Logik des Triggers verwendet werden, um zwischen einem einzelnen und einem Multirow-Einfügen zu unterscheiden.

-- 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;  

Siehe auch

DML-Trigger