Créer de déclencheurs DML pour gérer plusieurs lignes de données

Lorsque vous écrivez le code d’un déclencheur DML, considérez que l’instruction qui provoque le déclenchement du déclencheur peut être une instruction unique qui affecte plusieurs lignes de données, au lieu d’une seule ligne. Ce comportement est courant pour les déclencheurs UPDATE et DELETE, car ces instructions affectent fréquemment plusieurs lignes. Le comportement est moins courant pour les déclencheurs INSERT, car l’instruction INSERT de base ajoute une seule ligne. Toutefois, étant donné qu’un déclencheur INSERT peut être activé par une instruction INSERT INTO (table_name) SELECT, l’insertion de nombreuses lignes peut entraîner un seul appel de déclencheur.

Les considérations multi-lignes sont particulièrement importantes lorsque la fonction d’un déclencheur DML consiste à recalculer automatiquement les valeurs synthétiques d’une table et à stocker les résultats dans une autre pour des calculs continus.

Exemples

Les déclencheurs DML dans les exemples suivants sont conçus pour stocker un total cumulatif pour une colonne dans une autre table de la base de données d'exemple AdventureWorks2012.

A. Stockage d'un total cumulatif pour une insertion d'une seule ligne

La première version du déclencheur DML fonctionne bien pour une insertion à une seule ligne lorsqu’une ligne de données est chargée dans la PurchaseOrderDetail table. Une instruction INSERT déclenche le déclencheur DML et la nouvelle ligne est chargée dans la table insérée pendant la durée de l’exécution du déclencheur. L’instruction UPDATE lit la LineTotal valeur de colonne de la ligne et ajoute cette valeur à la valeur existante dans la SubTotal colonne de la PurchaseOrderHeader table. La WHERE clause garantit que la ligne mise à jour dans la PurchaseOrderDetail table correspond à PurchaseOrderID la ligne de la table insérée .

-- 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. Stocker un total cumulatif lors de l'insertion de plusieurs lignes ou d'une seule.

Pour une insertion multirow, le déclencheur DML dans l’exemple A peut ne pas fonctionner correctement ; l’expression à droite d’une expression d’affectation dans une instruction UPDATE (SubTotal + LineTotal) ne peut être qu’une seule valeur, et non une liste de valeurs. Par conséquent, l’effet du déclencheur consiste à récupérer une valeur à partir de n’importe quelle ligne unique de la table insérée et à ajouter cette valeur à la valeur existante SubTotal de la PurchaseOrderHeader table pour une valeur spécifique PurchaseOrderID . Cette opération peut ne pas avoir l’effet attendu si une valeur unique PurchaseOrderID s’est produite plusieurs fois dans la table insérée .

Pour mettre correctement à jour la PurchaseOrderHeader table, le déclencheur doit permettre la possibilité de plusieurs lignes dans la table insérée. Pour ce faire, utilisez la SUM fonction qui calcule le total LineTotal d’un groupe de lignes dans la table insérée pour chaque PurchaseOrderID. La SUM fonction est incluse dans une sous-requête corrélée (l’instruction SELECT entre parenthèses). Cette sous-requête retourne une valeur unique pour chaque PurchaseOrderID dans la table insérée qui correspond ou est corrélée à une PurchaseOrderID dans la table PurchaseOrderHeader.

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

Ce déclencheur fonctionne également correctement dans une insertion dans une seule ligne ; la somme de la valeur dans la colonne LineTotal correspond à celle d'une seule ligne. Toutefois, avec ce déclencheur, la sous-requête corrélée et l’opérateur IN utilisé dans la WHERE clause nécessitent un traitement supplémentaire à partir de SQL Server. Cela n’est pas nécessaire pour une insertion d'une seule ligne.

C. Stockage d’un total cumulatif en fonction du type d’insertion

Vous pouvez modifier le déclencheur pour utiliser la méthode optimale pour le nombre de lignes. Par exemple, la fonction @@ROWCOUNT peut être utilisée dans la logique du déclencheur pour faire la distinction entre une seule ligne et une insertion multi-ligne.

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

Voir aussi

Déclencheurs DML