Creazione di trigger DML per gestire più righe di datiCreate DML Triggers to Handle Multiple Rows of Data

Quando si scrive il codice di un trigger DML, è importante considerare che l'istruzione che attiva il trigger può essere singola e interessare più righe di dati, anziché una sola riga.When you write the code for a DML trigger, consider that the statement that causes the trigger to fire can be a single statement that affects multiple rows of data, instead of a single row. Questo funzionamento è comune per i trigger UPDATE e DELETE perché queste istruzioni in genere interessano più righe,This behavior is common for UPDATE and DELETE triggers because these statements frequently affect multiple rows. mentre è meno comune per i trigger INSERT perché l'istruzione INSERT di base aggiunge soltanto una riga singola.The behavior is less common for INSERT triggers because the basic INSERT statement adds only a single row. Dato però che un trigger INSERT può essere attivato da un'istruzione INSERT INTO (table_name) SELECT, l'inserimento di molte righe può causare la chiamata a un unico trigger.However, because an INSERT trigger can be fired by an INSERT INTO (table_name) SELECT statement, the insertion of many rows may cause a single trigger invocation.

Queste considerazioni sono di particolare importanza quando la funzione di un trigger DML consiste nel ricalcolare automaticamente i valori di riepilogo di una tabella e archiviare i risultati in un'altra tabella per i conteggi.Multirow considerations are especially important when the function of a DML trigger is to automatically recalculate summary values from one table and store the results in another for ongoing tallies.

Nota

Non è consigliabile utilizzare cursori nei trigger dato che possono potenzialmente ridurre le prestazioni.We do not recommend using cursors in triggers because they could potentially reduce performance. Per progettare un trigger che interessa più righe, utilizzare una logica basata su set di righe invece che su cursori.To design a trigger that affects multiple rows, use rowset-based logic instead of cursors.

EsempiExamples

I trigger DML negli esempi seguenti sono progettati per archiviare il totale parziale di una colonna in un'altra tabella del database di esempio di AdventureWorks2012AdventureWorks2012 .The DML triggers in the following examples are designed to store a running total of a column in another table of the AdventureWorks2012AdventureWorks2012 sample database.

A.A. Archiviazione di un totale parziale per l'inserimento di una riga singolaStoring a running total for a single-row insert

La prima versione del trigger DML funziona correttamente per l'inserimento di una singola riga, quando una riga di dati viene caricata nella tabella PurchaseOrderDetail .The first version of the DML trigger works well for a single-row insert when a row of data is loaded into the PurchaseOrderDetail table. Il trigger DML viene attivato da un'istruzione INSERT e la nuova riga viene caricata nella tabella inserted per la durata dell'esecuzione del trigger.An INSERT statement fires the DML trigger, and the new row is loaded into the inserted table for the duration of the trigger execution. L'istruzione UPDATE legge il valore della colonna LineTotal per la riga e lo aggiunge al valore esistente nella colonna SubTotal della tabella PurchaseOrderHeader .The UPDATE statement reads the LineTotal column value for the row and adds that value to the existing value in the SubTotal column in the PurchaseOrderHeader table. La clausola WHERE verifica che la riga aggiornata nella tabella PurchaseOrderDetail corrisponda al valore di PurchaseOrderID della riga nella tabella inserted .The WHERE clause makes sure that the updated row in the PurchaseOrderDetail table matches the PurchaseOrderID of the row in the inserted table.

-- Trigger is valid for single-row inserts.  
USE AdventureWorks2012;  
GO  
CREATE TRIGGER NewPODetail  
ON Purchasing.PurchaseOrderDetail  
AFTER INSERT AS  
   UPDATE PurchaseOrderHeader  
   SET SubTotal = SubTotal + LineTotal  
   FROM inserted  
   WHERE PurchaseOrderHeader.PurchaseOrderID = inserted.PurchaseOrderID ;  

B.B. Archiviazione di un totale parziale per l'inserimento di una riga singola o di più righeStoring a running total for a multirow or single-row insert

Nel caso di un'istruzione che interessa più righe, il trigger DML dell'esempio A potrebbe non funzionare correttamente. Nell'espressione a destra di un'espressione di assegnazione in un'istruzione UPDATE (SubTotal + LineTotal) è possibile includere un valore singolo, non un elenco di valori.For a multirow insert, the DML trigger in example A might not operate correctly; the expression to the right of an assignment expression in an UPDATE statement (SubTotal + LineTotal) can be only a single value, not a list of values. L'effetto del trigger è quindi quello di recuperare un valore dalle righe singole della tabella inserted e aggiungerlo al valore esistente SubTotal nella tabella PurchaseOrderHeader per un determinato valore di PurchaseOrderID .Therefore, the effect of the trigger is to retrieve a value from any single row in the inserted table and add that value to the existing SubTotal value in the PurchaseOrderHeader table for a specific PurchaseOrderID value. L'effetto dell'operazione potrebbe non essere quello atteso se un singolo valore di PurchaseOrderID compare più volte nella tabella inserted .This operation might not have the expected effect if a single PurchaseOrderID value occurred more than one time in the inserted table.

Per aggiornare la tabella PurchaseOrderHeader correttamente, il trigger deve tener conto della possibile presenza di più righe nella tabella inserted .To correctly update the PurchaseOrderHeader table, the trigger must allow for the chance of multiple rows in the inserted table. Ciò può essere ottenuto utilizzando la funzione SUM per calcolare il valore LineTotal totale relativo a un gruppo di righe nella tabella inserted per ogni PurchaseOrderID.You can do this by using the SUM function that calculates the total LineTotal for a group of rows in the inserted table for each PurchaseOrderID. La funzione SUM viene inclusa in una sottoquery correlata (l'istruzione SELECT in parentesi).The SUM function is included in a correlated subquery (the SELECT statement in parentheses). Questa sottoquery restituisce un singolo valore per ogni PurchaseOrderID nella tabella inserted che corrisponde o è correlato a un valore PurchaseOrderID nella tabella PurchaseOrderHeader .This subquery returns a single value for each PurchaseOrderID in the inserted table that matches or is correlated with a PurchaseOrderID in the PurchaseOrderHeader table.

-- Trigger is valid for multirow and single-row inserts.  
USE AdventureWorks2012;  
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);  

Questo trigger funziona inoltre correttamente per l'inserimento di una riga singola. La somma dei valori della colonna LineTotal corrisponde alla somma di una riga singola.This trigger also works correctly in a single-row insert; the sum of the LineTotal value column is the sum of a single row. Con questo trigger, tuttavia, la sottoquery correlata e l'operatore IN utilizzato nella clausola WHERE comportano un'ulteriore elaborazione in SQL ServerSQL Server,However, with this trigger the correlated subquery and the IN operator that is used in the WHERE clause require additional processing from SQL ServerSQL Server. non necessaria per l'inserimento di una riga singola.This is unnecessary for a single-row insert.

C.C. Archiviazione di un totale parziale sulla base del tipo di inserimentoStoring a running total based on the type of insert

È possibile modificare il trigger per utilizzare il metodo ottimale in base al numero di righe.You can change the trigger to use the method optimal for the number of rows. È ad esempio possibile utilizzare la funzione @@ROWCOUNT nella logica del trigger per distinguere tra l'inserimento di una riga singola e di più righe.For example, the @@ROWCOUNT function can be used in the logic of the trigger to distinguish between a single and a multirow insert.

-- Trigger valid for multirow and single row inserts  
-- and optimal for single row inserts.  
USE AdventureWorks2012;  
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;  

Vedere ancheSee Also

Trigger DMLDML Triggers