Observations au sujet des lignes multiples pour les déclencheurs DML

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 (table_name), 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 AdventureWorks.

A. 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 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. 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. Par conséquent, 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 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);

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

Voir aussi

Concepts

Mise en œuvre des déclencheurs DML

Aide et Informations

Assistance sur SQL Server 2005