Utiliser les tables inserted et deleted

Article
05/30/2023

S’applique à :SQL Server Azure SQL Database Azure SQL Managed Instance

Les instructions de déclenchement DML utilisent deux tables spéciales : la table deleted et la table inserted. SQL Server crée et gère automatiquement ces tables. Ces tables temporaires résidant en mémoire servent à tester les effets de certaines modifications de données et à définir des conditions pour les actions de déclencheur DML. Vous ne pouvez pas modifier directement les données contenues dans les tables ou effectuer des opérations DDL (Data Definition Language) sur les tables, telles que CREATE INDEX.

Présentation dans les tables inserted et deleted

Dans les déclencheurs DML, les tables inserted et deleted sont principalement utilisées pour exécuter les opérations suivantes :

étendre l'intégrité référentielle entre les tables ;
insérer ou mettre à jour des données dans des tables de base sous-jacentes d'une vue ;
déceler la présence d'erreurs et prendre les mesures nécessaires ;
détecter la différence entre l'état d'une table avant et après une modification des données, et prendre les mesures nécessaires en fonction de cette différence.

La table deleted stocke les copies des lignes affectées dans la table du déclencheur avant d’avoir été changées par une instruction DELETE ou UPDATE (la table du déclencheur est la table sur laquelle s’exécute le déclencheur DML). Pendant l’exécution d’une instruction DELETE ou UPDATE, les lignes affectées sont d’abord copiées à partir de la table du déclencheur puis transférées à la table deleted.

La table inserted stocke les copies des lignes nouvelles ou modifiées après une instruction INSERT ou UPDATE. Au cours de l’exécution d’une instruction INSERT ou UPDATE, les lignes nouvelles ou modifiées dans la table du déclencheur sont copiées dans la table inserted. Les lignes de la table inserted sont les copies des lignes nouvelles ou mises à jour dans la table du déclencheur.

Une transaction de mise à jour est similaire à une opération de suppression suivie d’une opération d’insertion. Lors de l’exécution d’une instruction UPDATE, la séquence d’événements suivante se produit :

La ligne d’origine est copiée de la table du déclencheur vers la table deleted.
La table du déclencheur est mise à jour avec les nouvelles valeurs de l’instruction UPDATE.
La ligne mise à jour dans la table du déclencheur est copiée dans la table inserted.

Cela vous permet de comparer le contenu de la ligne avant la mise à jour (dans la table deleted) avec les nouvelles valeurs de la ligne après la mise à jour (dans la table inserted).

Pour définir les conditions du déclencheur, utilisez les tables inserted et deleted de façon appropriée, en fonction de l'action qui a activé le déclencheur. Bien que vous puissiez, sans provoquer d'erreur, référencer la table deleted pendant le test d'une insertion (INSERT) ou la table inserted pendant le test d'une suppression (DELETE), ces tables de test du déclencheur ne contiendront alors aucune ligne.

Note

Si les actions du déclencheur dépendent du nombre de lignes affectées par une modification de données, utilisez les tests (comme l'examen de @@ROWCOUNT) pour les modifications de données multilignes (une instruction INSERT, DELETE ou UPDATE basée sur une instruction SELECT), puis effectuez les opérations appropriées. Pour plus d’informations, consultez Créer de déclencheurs DML pour gérer plusieurs lignes de données.

SQL Server n’autorise pas les références de texte, ntext ou de colonne d’image dans les tables insérées et supprimées pour les déclencheurs AFTER. Cependant, ces types de données sont inclus à des fins de compatibilité ascendante uniquement. Pour le stockage des données volumineuses, il est préférable d’utiliser les types de données varchar(max), nvarchar(max)et varbinary(max) . Les déclencheurs AFTER et INSTEAD OF prennent en charge les données varchar(max), nvarchar(max)et varbinary(max) dans les tables inserted et deleted. Pour plus d’informations, consultez CREATE TRIGGER (Transact-SQL).

Exemple : Utiliser la table inserted dans un déclencheur pour appliquer des règles métier

Les contraintes CHECK pouvant référencer uniquement les colonnes sur lesquelles des contraintes de niveau table ou colonne sont définies, toutes les contraintes entre tables (dans ce cas, des règles de gestion) doivent être définies sous la forme de déclencheurs.

L'exemple suivant crée un déclencheur DML. Ce déclencheur vérifie que les informations de conditions de crédit du fournisseur sont correctes lors d'une tentative d'insertion d'un nouveau bon de commande dans la table PurchaseOrderHeader . Pour obtenir les informations de conditions de crédit du fournisseur correspondant à la commande qui vient d'être insérée, la table Vendor doit être référencée et jointe à la table inserted. Si les conditions de crédit sont trop faibles, un message s'affiche et l'insertion n'a pas lieu.

USE AdventureWorks2022;
GO
IF OBJECT_ID ('Purchasing.LowCredit','TR') IS NOT NULL
   DROP TRIGGER Purchasing.LowCredit;
GO
-- This trigger prevents a row from being inserted in the Purchasing.PurchaseOrderHeader table
-- when the credit rating of the specified vendor is set to 5 (below average).  
  
CREATE TRIGGER Purchasing.LowCredit ON Purchasing.PurchaseOrderHeader  
AFTER INSERT  
AS  
IF (ROWCOUNT_BIG() = 0)
RETURN;
IF EXISTS (SELECT 1  
           FROM inserted AS i   
           JOIN Purchasing.Vendor AS v   
           ON v.BusinessEntityID = i.VendorID  
           WHERE v.CreditRating = 5  
          )  
BEGIN  
RAISERROR ('A vendor''s credit rating is too low to accept new  
purchase orders.', 16, 1);  
ROLLBACK TRANSACTION;  
RETURN   
END;  
GO  
  
-- This statement attempts to insert a row into the PurchaseOrderHeader table  
-- for a vendor that has a below average credit rating.  
-- The AFTER INSERT trigger is fired and the INSERT transaction is rolled back.  
  
INSERT INTO Purchasing.PurchaseOrderHeader (RevisionNumber, Status, EmployeeID,  
VendorID, ShipMethodID, OrderDate, ShipDate, SubTotal, TaxAmt, Freight)  
VALUES (  
2  
,3  
,261  
,1652  
,4  
,GETDATE()  
,GETDATE()  
,44594.55  
,3567.564  
,1114.8638 );  
GO

Utiliser les tables inserted et deleted dans les déclencheurs INSTEAD OF

Les tables inserted et deleted passées aux déclencheurs INSTEAD OF définis sur des tables suivent les mêmes règles que les tables inserted et deleted passées aux déclencheurs AFTER. Le format des tables inserted et deleted est le même que celui de la table sur laquelle est défini le déclencheur INSTEAD OF. Chaque colonne des tables inserted et deleted est directement mappée à une colonne de la table de base.

Qu'une table possède ou non un déclencheur INSTEAD OF, les règles suivantes qui régissent la fourniture de valeurs pour les colonnes par une instruction INSERT ou UPDATE faisant référence à la table sont les mêmes :

Aucune valeur ne peut être spécifiée pour une colonne calculée ou de type de données timestamp .
Aucune valeur ne peut être spécifiée pour les colonnes possédant une propriété IDENTITY, à moins que IDENTITY_INSERT ait la valeur ON pour cette table, auquel cas les instructions INSERT doivent fournir une valeur.
Les instructions INSERT doivent fournir des valeurs pour toutes les colonnes NOT NULL pour lesquelles aucune contrainte DEFAULT n'est définie.
Les valeurs sont facultatives pour toute colonne acceptant des valeurs NULL ou toute colonne NOT NULL avec valeur par défaut (DEFAULT), sous réserve qu'il ne s'agisse pas d'une colonne calculée, identité ou timestamp .

Lorsqu’une instruction INSERT, UPDATE ou DELETE fait référence à une vue qui a un déclencheur INSTEAD OF, le moteur de base de données appelle le déclencheur au lieu d’effectuer une action directe sur n’importe quelle table. Le déclencheur doit utiliser les informations présentées dans les tables inserted et deleted pour élaborer toute instruction nécessaire à l'implémentation de l'action requise dans les tables de base, même si le format des informations contenues dans les tables inserted et deleted conçues pour la vue diffère de celui des données stockées dans les tables de base.

Le format des tables inserted et deleted passées à un déclencheur INSTEAD OF défini sur une vue correspond à la liste de sélection de l'instruction SELECT définie pour la vue. Par exemple :

USE AdventureWorks2022;  
GO  
CREATE VIEW dbo.EmployeeNames (BusinessEntityID, LName, FName)  
AS  
SELECT e.BusinessEntityID, p.LastName, p.FirstName  
FROM HumanResources.Employee AS e   
JOIN Person.Person AS p  
ON e.BusinessEntityID = p.BusinessEntityID;

Le jeu de résultats pour cette vue possède trois colonnes : une colonne int et deux colonnes nvarchar . Les tables inserted et deleted passées à un déclencheur INSTEAD OF défini sur la vue ont également une colonne int nommée BusinessEntityID, une colonne nvarchar nommée LNameet une colonne nvarchar nommée FName.

La liste de sélection d'une vue peut également contenir des expressions qui n'établissent pas de mappage direct à une colonne de table de base unique. Certaines expressions de vue, telles que l'invocation d'une constante ou d'une fonction, peuvent ne pas référencer de colonne et être ignorées. Les expressions complexes peuvent référencer plusieurs colonnes, mais les tables inserted et deleted ne détiennent qu'une seule valeur pour chaque ligne insérée. Les mêmes considérations s'appliquent aux expressions simples d'une vue si elles font référence à une colonne calculée à laquelle est associée une expression complexe. Un déclencheur INSTEAD OF défini sur la vue doit gérer ces types d'expressions.

Considérations relatives aux performances

Étant donné que les tables inserted et deleted sont des tables virtuelles, résidant dans la mémoire, les propriétés telles que les statistiques ou les index ne sont pas disponibles. Bien que certaines informations de cardinalité soient exposées à partir de ces tables, vous devez faire preuve de prudence lors de l’examen du nombre de lignes à stocker temporairement ici. L’insertion d’un grand nombre de lignes dans ces tables et leur interrogation ou leur jonction avec d’autres tables peuvent entraîner des plans de requête non optimaux et des exécutions de requêtes lentes. Veillez à concevoir et à tester soigneusement votre application pour répondre à vos besoins en matière de performances de requête.

Étapes suivantes

Pour plus d’informations, consultez la vue d’ensemble des Déclencheurs DML.