Tworzenie wyzwalaczy DML do obsługi wielu wierszy danych

Dotyczy:SQL ServerAzure SQL DatabaseAzure SQL Managed Instance

Podczas pisania kodu wyzwalacza DML należy wziąć pod uwagę, że instrukcja, która powoduje wyzwolenie wyzwalacza, może być pojedynczą instrukcją, która ma wpływ na wiele wierszy danych, zamiast jednego wiersza. To zachowanie jest typowe dla wyzwalaczy UPDATE i DELETE, ponieważ te instrukcje często mają wpływ na wiele wierszy. Zachowanie jest mniej powszechne w przypadku wyzwalaczy INSERT, ponieważ podstawowa instrukcja INSERT dodaje tylko jeden wiersz. Jednak ponieważ wyzwalacz INSERT może zostać wyzwolony przez instrukcję INSERT INTO (table_name) SELECT, wstawianie wielu wierszy może spowodować wywołanie pojedynczego wyzwalacza.

Zagadnienia dotyczące wielowierszowego przetwarzania są szczególnie ważne, gdy funkcja wyzwalacza DML polega na automatycznym ponownym obliczaniu wartości podsumowania z jednej tabeli i przechowywaniu wyników w innej tabeli do bieżącego zestawienia.

Notatka

Nie zalecamy używania kursorów w wyzwalaczach, ponieważ mogą one potencjalnie zmniejszyć wydajność. Aby zaprojektować wyzwalacz, który ma wpływ na wiele wierszy, użyj logiki opartej na zestawie wierszy zamiast kursorów.

Przykłady

Wyzwalacze DML w poniższych przykładach zostały zaprojektowane tak, aby przechowywać sumę bieżącą kolumny w innej tabeli przykładowej bazy danych AdventureWorks2022.

A. Przechowywanie bieżącej sumy podczas wstawiania pojedynczego wiersza

Pierwsza wersja wyzwalacza DML działa dobrze w przypadku wstawiania pojedynczego wiersza, gdy wiersz danych jest ładowany do tabeli PurchaseOrderDetail. Instrukcja INSERT uruchamia wyzwalacz DML, a nowy wiersz jest ładowany do tabeli wstawionej na czas wykonywania wyzwalacza. Instrukcja UPDATE odczytuje wartość kolumny LineTotal dla wiersza i dodaje wartość do istniejącej wartości w kolumnie SubTotal w tabeli PurchaseOrderHeader. Klauzula WHERE zapewnia, że zaktualizowany wiersz w tabeli PurchaseOrderDetail jest zgodny z PurchaseOrderID wiersza w tabeli wstawionej.

-- 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. Przechowywanie bieżącej sumy dla wstawienia wielowierszowego lub jednowierszowego

W przypadku wstawiania wielorowowego wyzwalacz DML w przykładzie A może nie działać poprawnie; wyrażenie z prawej strony wyrażenia przypisania w instrukcji UPDATE (SubTotal + LineTotal) może być tylko jedną wartością, a nie listą wartości. W związku z tym efektem wyzwalacza jest pobranie wartości z dowolnego wiersza w tabeli wstawionej i dodanie tej wartości do istniejącej wartości SubTotal w tabeli PurchaseOrderHeader dla określonej wartości PurchaseOrderID. Ta operacja może nie mieć oczekiwanego efektu, jeśli jedna wartość PurchaseOrderID wystąpiła więcej niż raz w tabeli wstawionej.

Aby poprawnie zaktualizować tabelę PurchaseOrderHeader, wyzwalacz musi umożliwiać istnienie wielu wierszy w wstawionej tabeli . W tym celu można użyć funkcji SUM, która oblicza łączną LineTotal dla grupy wierszy w tabeli wstawionej w dla każdego PurchaseOrderID. Funkcja SUM jest uwzględniona w skorelowanym podzapytaniu (instrukcja SELECT w nawiasach). Ta podzapytywanie zwraca pojedynczą wartość dla każdej PurchaseOrderID w tabeli wstawionej, która jest zgodna lub jest skorelowana z PurchaseOrderID w tabeli 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);  

Ten mechanizm wyzwalający działa również poprawnie przy wstawianiu pojedynczego wiersza; suma kolumny wartości LineTotal jest sumą pojedynczego wiersza. Jednak w przypadku tego wyzwalacza skorelowane podzapytanie i operator IN używany w klauzuli WHERE wymagają dodatkowego przetwarzania przez serwer SQL. Nie jest to potrzebne w przypadku wstawiania pojedynczego wiersza.

C. Przechowywanie sumy bieżącej w zależności od typu insertu

Wyzwalacz można zmienić, aby użyć metody optymalnej dla liczby wierszy. Na przykład funkcja @@ROWCOUNT może służyć w logice wyzwalacza do rozróżnienia między wstawianiem pojedynczego wiersza a wielowierszowym.

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

Zobacz też

Wyzwalacze DML