Vytvoření triggerů DML pro zpracování více řádků dat

platí pro:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceSQL databáze v Microsoft Fabric

Při psaní kódu pro trigger DML vezměte v úvahu, že příkaz, který způsobí aktivaci triggeru, může být jediný příkaz, který ovlivňuje více řádků dat, a ne jeden řádek. Toto chování je běžné u triggerů UPDATE a DELETE, protože tyto příkazy často ovlivňují více řádků. Chování je méně běžné pro triggery INSERT, protože základní příkaz INSERT přidá pouze jeden řádek. Vzhledem k tomu, že trigger INSERT lze aktivovat příkazem INSERT INTO (table_name) SELECT, může vložení mnoha řádků způsobit vyvolání jediné aktivační události.

Zvážení více řádků je zvláště důležité, když funkce triggeru DML automaticky přepočítává souhrnné hodnoty z jedné tabulky a ukládá výsledky do druhé pro průběžné součty.

Poznámka

Nedoporučujeme používat kurzory v triggerech, protože by mohly potenciálně snížit výkon. Pokud chcete navrhnout trigger, který ovlivňuje více řádků, použijte místo kurzorů logiku založenou na sadě řádků.

Příklady

Triggery DML v následujících příkladech jsou navržené tak, aby ukládaly průběžný součet sloupce v jiné tabulce ukázkové databáze AdventureWorks2025.

A. Uložení průběžného součtu pro vložení jednoho řádku

První verze DML triggeru funguje dobře pro vložení jednoho řádku při načítání řádku dat do tabulky PurchaseOrderDetail. Příkaz INSERT aktivuje trigger DML a nový řádek se načte do vložené tabulky po dobu trvání spuštění triggeru. Příkaz UPDATE přečte hodnotu sloupce LineTotal řádku a přidá ji k existující hodnotě ve sloupci SubTotal v tabulce PurchaseOrderHeader. Klauzule WHERE zajišťuje, že aktualizovaný řádek v tabulce PurchaseOrderDetail odpovídá PurchaseOrderID řádku v vložené tabulce.

-- 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. Ukládání průběžného součtu pro vložení s více řádky nebo jednořádkové vložení

U víceradkového vložení nemusí spouštěč DML v příkladu A správně fungovat; výraz na pravé straně výrazového přiřazení v příkazu UPDATE (SubTotal + LineTotal) může být pouze jedna hodnota, nikoli seznam hodnot. Výsledkem triggeru je načtení hodnoty z libovolného řádku v vložené tabulce a přidání této hodnoty do existující hodnoty SubTotal v tabulce PurchaseOrderHeader pro konkrétní hodnotu PurchaseOrderID. Tato operace nemusí mít očekávaný účinek, pokud se hodnota PurchaseOrderID objevila více než jednou v tabulce vložené do.

Aby bylo možné správně aktualizovat tabulku PurchaseOrderHeader, aktivační událost musí umožňovat možnost více řádků v vložené tabulce. Můžete to provést pomocí funkce SUM, která vypočítá celkovou LineTotal pro skupinu řádků v vložené tabulky pro každou PurchaseOrderID. Funkce SUM je součástí korelovaného poddotazu (příkaz SELECT v závorkách). Tento poddotaz vrátí jednu hodnotu pro každou PurchaseOrderID v tabulce vložené do, která odpovídá nebo je spojena s PurchaseOrderID v tabulce 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);  

Tento trigger také funguje správně při vložení jediného řádku; součet sloupce LineTotal je součet jednoho řádku. S tímto triggerem však korelovaný poddotaz a operátor IN, který se používá v klauzuli WHERE, vyžadují další zpracování z SQL Serveru. Není to nutné pro vložení jednoho řádku.

C. Ukládání průběžných součtů na základě typu vložení

Můžete změnit spouštěč na metodu optimální pro počet řádků. Například funkci @@ROWCOUNT lze použít v logice triggeru k rozlišení mezi jednořádkovým a víceřádkovým vložením.

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

Viz také

DML spouště