DML-triggers maken voor het afhandelen van meerdere rijen met gegevens

Artikel
2025-03-05

van toepassing op:SQL Server Azure SQL Database Azure SQL Managed Instance

Wanneer u de code voor een DML-trigger schrijft, moet u er rekening mee houden dat de instructie die ervoor zorgt dat de trigger wordt geactiveerd, één instructie kan zijn die van invloed is op meerdere rijen met gegevens, in plaats van één rij. Dit gedrag is gebruikelijk voor UPDATE- en DELETE-triggers, omdat deze instructies vaak van invloed zijn op meerdere rijen. Het gedrag is minder gebruikelijk voor INSERT-triggers, omdat de eenvoudige INSERT-instructie slechts één rij toevoegt. Omdat een INSERT-trigger echter kan worden geactiveerd door een INSERT INTO-instructie (table_name) SELECT, kan de invoeging van veel rijen één triggeraanroep veroorzaken.

Multirow-overwegingen zijn vooral belangrijk wanneer de functie van een DML-trigger is om automatisch samenvattingswaarden uit de ene tabel opnieuw te berekenen en de resultaten op te slaan in een andere voor lopende totalen.

Notitie

Het wordt afgeraden om cursors in triggers te gebruiken, omdat ze mogelijk de prestaties kunnen verminderen. Als u een trigger wilt ontwerpen die van invloed is op meerdere rijen, gebruikt u logica op basis van rijen in plaats van cursors.

Voorbeelden

De DML-triggers in de volgende voorbeelden zijn ontworpen voor het opslaan van een voorlopig totaal van een kolom in een andere tabel van de AdventureWorks2022 voorbeelddatabase.

Een. Een voorlopig totaal opslaan voor een invoegbewerking met één rij

De eerste versie van de DML-trigger werkt goed voor een invoegbewerking met één rij wanneer een rij gegevens in de PurchaseOrderDetail tabel wordt geladen. Met een INSERT-instructie wordt de DML-trigger geactiveerd en wordt de nieuwe rij geladen in de ingevoegd tabel voor de duur van de uitvoering van de trigger. De instructie UPDATE leest de LineTotal kolomwaarde voor de rij en voegt die waarde toe aan de bestaande waarde in de SubTotal kolom in de PurchaseOrderHeader tabel. De WHERE-component zorgt ervoor dat de bijgewerkte rij in de PurchaseOrderDetail tabel overeenkomt met de PurchaseOrderID van de rij in de ingevoegde tabel.

-- 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. Een voorlopig totaal opslaan voor een invoegbewerking met meerdere rijen of één rij

Voor een multirow insert werkt de DML-trigger in voorbeeld A mogelijk niet goed; de expressie rechts van een toewijzingsexpressie in een UPDATE-instructie (SubTotal + LineTotal) kan slechts één waarde zijn, niet een lijst met waarden. Daarom is het effect van de trigger om een waarde op te halen uit één rij in de ingevoegd tabel en die waarde toe te voegen aan de bestaande SubTotal waarde in de PurchaseOrderHeader tabel voor een specifieke PurchaseOrderID waarde. Deze bewerking heeft mogelijk niet het verwachte effect als één PurchaseOrderID waarde meer dan één keer heeft plaatsgevonden in de ingevoegde tabel.

Als u de PurchaseOrderHeader tabel correct wilt bijwerken, moet de trigger de kans op meerdere rijen in de ingevoegde tabel toestaan. U kunt dit doen met behulp van de functie SUM waarmee het totale LineTotal voor een groep rijen in de ingevoegde tabel voor elke PurchaseOrderIDwordt berekend. De SUM-functie is opgenomen in een gecorreleerde subquery (de SELECT instructie tussen haakjes). Deze subquery retourneert één waarde voor elke PurchaseOrderID in de ingevoegde tabel die overeenkomt met of is gecorreleerd met een PurchaseOrderID in de PurchaseOrderHeader tabel.

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

Deze trigger werkt ook correct in een invoeging met één rij; de som van de kolom LineTotal waarde is de som van één rij. De gecorreleerde subquery en de IN-operator die in de WHERE-clausule wordt gebruikt, vereisen echter aanvullende verwerking van SQL Server als gevolg van deze trigger. Dit is niet nodig voor het invoegen van één rij.

C. Een lopend totaal opslaan op basis van het type invoeging

U kunt de trigger wijzigen om de methode optimaal te gebruiken voor het aantal rijen. De functie @@ROWCOUNT kan bijvoorbeeld worden gebruikt in de logica van de trigger om onderscheid te maken tussen één en een multirow insert.

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

Zie ook

DML-triggers

Delen via