Vytvoření triggerů DML pro zpracování více řádků dat
platí pro:
SQL ServerAzure SQL Databaseazure SQL Managed Instance
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 AdventureWorks2022.
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;