Skapa DML-utlösare för att hantera flera rader med data

gäller för:SQL ServerAzure SQL DatabaseAzure SQL Managed Instance

När du skriver koden för en DML-utlösare bör du tänka på att instruktionen som utlöser utlösaren kan vara en enda instruktion som påverkar flera rader med data i stället för en enda rad. Det här beteendet är vanligt för UPDATE- och DELETE-utlösare eftersom dessa instruktioner ofta påverkar flera rader. Beteendet är mindre vanligt för INSERT-utlösare eftersom den grundläggande INSERT-instruktionen bara lägger till en enskild rad. Men eftersom en INSERT-utlösare kan utlösas av en INSERT INTO- (table_name) SELECT-instruktion kan införandet av många rader orsaka en enda utlösaranrop.

Överväganden för flera rader är särskilt viktiga när funktionen för en DML-utlösare är att automatiskt beräkna om sammanfattningsvärden från en tabell och lagra resultaten i en annan för pågående summeringar.

Exempel

DML-utlösarna i följande exempel är utformade för att lagra en löpande summa av en kolumn i en annan tabell i AdventureWorks2022 exempeldatabas.

A. Lagra en löpande summa för en infogad enskild rad

Den första versionen av DML-utlösaren fungerar bra för infog av en enskild rad när data läses in i tabellen PurchaseOrderDetail. En INSERT-instruktion utlöser DML-utlösaren och den nya raden läses in i infogad tabell under hela utlösarkörningen. Instruktionen UPDATE läser LineTotal kolumnvärdet för raden och lägger till det värdet i det befintliga värdet i kolumnen SubTotal i tabellen PurchaseOrderHeader. Satsen WHERE ser till att den uppdaterade raden i tabellen PurchaseOrderDetail matchar radens PurchaseOrderID i infogad tabell.

-- 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. Lagra en löpande uppdaterad summa vid en fler- eller enfogning av rader

För en multirow-infogning kanske DML-utlösaren i exempel A inte fungerar korrekt. uttrycket till höger om ett tilldelningsuttryck i en UPDATE-instruktion (SubTotal + LineTotal) kan bara vara ett enda värde, inte en lista med värden. Utlösarens effekt är därför att hämta ett värde från en enskild rad i den infogade-tabellen och lägga till det värdet i det befintliga SubTotal-värdet i tabellen PurchaseOrderHeader för ett specifikt PurchaseOrderID värde. Den här åtgärden kanske inte har den förväntade effekten om ett enda PurchaseOrderID värde inträffade mer än en gång i infogade tabell.

Om du vill uppdatera tabellen PurchaseOrderHeader korrekt måste utlösaren tillåta möjligheten till flera rader i den infogade tabellen . Du kan göra detta genom att använda funktionen SUM, som beräknar den totala LineTotal för en grupp rader i den -tabell som är infogad i, för varje PurchaseOrderID. Funktionen SUM ingår i en korrelerad underfråga (SELECT-instruktionen i parenteser). Den här underfrågan returnerar ett enda värde för varje PurchaseOrderID i infogad tabell som matchar eller korreleras med en PurchaseOrderID i tabellen 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);  

Den här utlösaren fungerar också korrekt vid en infogning i en rad; summan av värdekolumnen LineTotal är då summan av en enskild rad. Men med den här utlösaren kräver den korrelerade underfrågan och IN-operatorn som används i WHERE-satsen ytterligare bearbetning från SQL Server. Detta är inte nödvändigt för införande av en enskild rad.

C. Lagra en löpande summa baserat på typen av infogning

Du kan ändra utlösaren så att metoden används optimalt för antalet rader. Till exempel kan funktionen @@ROWCOUNT användas i utlösarens logik för att skilja mellan en enskild och en flerradig infogning.

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

Se även

DML-utlösare