Multirow Considerations for DML Triggers
Podczas pisania kodu wyzwalaczy DML uznają, że instrukcja, która powoduje, że wyzwalacz uruchomienie może pojedynczej instrukcji, które mają wpływ na wiele wierszy zawierających dane, a nie korzystać z pojedynczego wiersza.To zachowanie jest typowe aktualizacji i usuwania wyzwalaczy, ponieważ instrukcje te często wpływa na wiele wierszy.Zachowanie jest mniej popularne INSERT wyzwalaczy, ponieważ podstawowy instrukcja INSERT dodaje tylko jeden wiersz.Jednak ponieważ wyzwalacza WSTAWIENIA może być uruchamiany przez (INSERT INTOtable_name) Instrukcja SELECT, wstawiania wielu wierszy może spowodować, że wywołania jednego wyzwalacza.
MultiRow zagadnienia są szczególnie ważne, gdy wyzwalacz DML funkcja automatyczne ponowne obliczanie wartości podsumowań z jednej tabela i zapisać wyniki w innej na stałe tallies.
Uwaga
Nie zaleca się używanie kursorów w wyzwalaczy, ponieważ one potencjalnie może zmniejszyć wydajność.Aby zaprojektować wyzwalacz, który ma wpływ na wiele zestaw wierszy, należy użyć oparte na zestawie zestaw wierszy logiki zamiast kursorów.
Przykłady
Wyzwalacze DML w poniższych przykładach zaprojektowano tak, aby sumy kolumna są przechowywane w innej tabela z AdventureWorks Przykładowa baza danych.
A.Przechowywanie sumę dla wstawiania pojedynczych wierszy
Pierwsza wersja wyzwalacz DML działa dobrze, aby wstawić pojedynczy wiersz, gdy wiersz danych jest ładowany do PurchaseOrderDetail Tabela. Instrukcja INSERT uruchomieniu wyzwalacz DML i nowy wiersz jest ładowany do dodaje tabela na czas wykonywania wyzwalacza.The UPDATE instrukcja reads the LineTotal kolumna value for the row and adds that value to the existing value in the SubTotal kolumna in the PurchaseOrderHeader tabela. The WHERE klauzula makes sure that the updated row in the PurchaseOrderDetail tabela matches the PurchaseOrderID of the row in the inserted tabela.
-- Trigger is valid for single-row inserts.
USE AdventureWorks;
GO
CREATE TRIGGER NewPODetail
ON Purchasing.PurchaseOrderDetail
AFTER INSERT AS
UPDATE PurchaseOrderHeader
SET SubTotal = SubTotal + LineTotal
FROM inserted
WHERE PurchaseOrderHeader.PurchaseOrderID = inserted.PurchaseOrderID ;
B.Przechowywanie sumę dla multirow lub jednego wiersza wstawiania
Do wstawiania multirow wyzwalacz DML w przykładzie A może nie działać poprawnie; wyrażenie po prawej stronie wyrażenia przypisania w (Instrukcja UPDATESubTotal + LineTotal) mogą być tylko jedną wartość, nie jest lista wartości. W związku z tym, efekt wyzwalacza ma pobierać wartości z dowolnego pojedynczego wiersza w dodaje tabeli i dodać ją do istniejącej SubTotal wartość w PurchaseOrderHeader Tabela dla określonego PurchaseOrderID wartość. Ta operacja może nie mieć oczekiwanego efektu, jeśli pojedyncze PurchaseOrderID wartość wystąpił więcej niż jeden raz w dodaje tabela.
Aby poprawnie zaktualizować PurchaseOrderHeader Tabela, wyzwalacz musi pozwalać na ryzyko wystąpienia wielu wierszy dodaje tabeli.Można to zrobić za pomocą SUM Funkcja, która oblicza sumę LineTotal dla grupy wierszy w dodaje Tabela każdego PurchaseOrderID. The SUM funkcja is included in a skorelowana podkwerenda (the SELECT instrukcja in parentheses). Podkwerenda to zwraca jedną wartość dla każdego PurchaseOrderID w dodaje tabelę, która jest zgodna, lub jest skorelowany z PurchaseOrderID w PurchaseOrderHeader Tabela.
-- Trigger is valid for multirow and single-row inserts.
USE AdventureWorks;
GO
CREATE TRIGGER NewPODetail2
ON Purchasing.PurchaseOrderDetail
AFTER INSERT AS
UPDATE PurchaseOrderHeader
SET SubTotal = SubTotal +
(SELECT SUM(LineTotal)
FROM inserted
WHERE PurchaseOrderHeader.PurchaseOrderID
= inserted.PurchaseOrderID)
WHERE PurchaseOrderHeader.PurchaseOrderID IN
(SELECT PurchaseOrderID FROM inserted);
Ten wyzwalacz działa poprawnie w pojedynczym wierszu wstawiania; sumę LineTotal wartość kolumna jest sumą pojedynczego wiersza. Jednak z tym wyzwalać skorelowana podkwerenda oraz IN operator, który jest używany w WHERE Klauzula wymagać dodatkowego przetwarzania z SQL Server. Jest to konieczne w przypadku wstawiania pojedynczych wierszy.
C.Przechowywanie sumę na podstawie typu wstawiania
Można zmienić wyzwalacz ma korzystać z metoda optymalne dla liczby wierszy.Na przykład @@ROWCOUNT funkcja może być używana w logice wyzwalacz rozróżnienie między jednym a multirow wstawiania.
-- Trigger valid for multirow and single row inserts
-- and optimal for single row inserts.
USE AdventureWorks;
GO
CREATE TRIGGER NewPODetail3
ON Purchasing.PurchaseOrderDetail
FOR INSERT AS
IF @@ROWCOUNT = 1
BEGIN
UPDATE PurchaseOrderHeader
SET SubTotal = SubTotal + LineTotal
FROM inserted
WHERE PurchaseOrderHeader.PurchaseOrderID = inserted.PurchaseOrderID
END
ELSE
BEGIN
UPDATE PurchaseOrderHeader
SET SubTotal = SubTotal +
(SELECT SUM(LineTotal)
FROM inserted
WHERE PurchaseOrderHeader.PurchaseOrderID
= inserted.PurchaseOrderID)
WHERE PurchaseOrderHeader.PurchaseOrderID IN
(SELECT PurchaseOrderID FROM inserted)
END;