Créer de déclencheurs DML pour gérer plusieurs lignes de données
S’applique à : 
SQL Server Azure SQL Database Azure SQL Managed Instance
Lors de l'écriture du code d'un déclencheur DML, tenez compte du fait que l'instruction qui active le déclencheur peut être une instruction unique concernant plusieurs lignes de données au lieu d'une seule. Ce comportement est courant pour les déclencheurs UPDATE et DELETE qui concernent souvent plusieurs lignes. Il est moins fréquent dans le cas des déclencheurs INSERT car l'instruction INSERT de base n'ajoute qu'une seule ligne. Toutefois, comme un déclencheur INSERT peut être activé par une instruction SELECT INSERT INTO (nom_table), l’insertion de nombreuses lignes peut aboutir à un appel de déclencheur unique.
Les facteurs à prendre en compte au sujet des lignes multiples sont particulièrement importants lorsque la fonction d'un déclencheur DML consiste à recalculer automatiquement les totaux d'une table et à enregistrer les résultats dans une autre table en vue de subir d'autres calculs.
Remarque
Il n'est pas conseillé d'utiliser les curseurs dans le déclencheur, car ils pourraient réduire les performances. Pour concevoir un déclencheur portant sur plusieurs lignes, utilisez au lieu des curseurs une logique basée sur un ensemble de lignes.
Exemples
Les déclencheurs DML des exemples suivants sont conçus pour stocker le total cumulé d'une colonne dans une autre table de l'exemple de bases de données AdventureWorks2022 .
R : Stockage d'un total cumulé pour l'insertion d'une seule ligne
La première version du déclencheur DML fonctionne correctement pour l'insertion d'une seule ligne, lorsqu'une ligne de données est chargée dans la table PurchaseOrderDetail . Le déclencheur DML est activé par une instruction INSERT et la nouvelle ligne est chargée dans la table inserted pendant la durée d’exécution du déclencheur. L'instruction UPDATE lit la valeur de la colonne LineTotal correspondant à la ligne et l'ajoute à la valeur existante dans la colonne SubTotal de la table PurchaseOrderHeader . La clause WHERE garantit que la ligne mise à jour dans la table PurchaseOrderDetail correspond à la valeur PurchaseOrderID de la ligne dans la table inserted .
-- 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. Stockage d'un total cumulé pour l'insertion d'une ou de plusieurs lignes
Dans le cas d’une insertion de plusieurs lignes, le déclencheur DML de 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 unique valeur, et non une liste de valeurs. Ainsi, le déclencheur extrait une valeur à partir d’une seule ligne de la table inserted et l’ajoute à la valeur SubTotal existante de la table PurchaseOrderHeader correspondant à une valeur PurchaseOrderID spécifique. Cette opération risque de ne pas produire l’effet escompté si une valeur unique PurchaseOrderID se trouve plus d’une fois dans la table inserted .
Pour mettre à jour correctement la table PurchaseOrderHeader , le déclencheur doit tenir compte de l’existence possible de plusieurs lignes dans la table inserted . Pour ce faire, vous pouvez utiliser la fonction SUM , qui calcule le total LineTotal d’un groupe de lignes de la table inserted pour chaque PurchaseOrderID. La fonction SUM 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 de la table inserted qui correspond ou est corrélée à PurchaseOrderID dans la table 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);
Le fonctionnement de ce déclencheur est également correct dans le cas de l'insertion d'une seule ligne ; le total de la colonne LineTotal est alors la somme d'une seule ligne. La sous-requête corrélée et l'opérateur IN utilisé dans la clause WHERE demandent toutefois un traitement complémentaire de SQL Server. ce qui n'est pas nécessaire pour l'insertion d'une seule ligne.
C. Stockage d'un total cumulé basé sur le type d'insertion
Pour rectifier ce problème, vous pouvez modifier le déclencheur afin d'utiliser la méthode optimale en fonction du nombre de lignes. Par exemple, la fonction @@ROWCOUNT peut être utilisée dans la logique du déclencheur pour différencier une insertion d'une seule ligne d'une insertion de plusieurs lignes.
-- 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;