Utiliser les tables inserted et deleted

S’applique à :SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceBase de données SQL dans Microsoft Fabric

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 dans les tables ni effectuer des opérations de langage de définition de données (DDL) 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 supprimée stocke des copies des lignes affectées dans la table des déclencheurs avant qu’elles ne soient modifiées par une DELETE instruction ou UPDATE (la table des déclencheurs est la table sur laquelle le déclencheur DML s’exécute). Lors de l’exécution d’une DELETE instruction ou, UPDATE les lignes affectées sont d’abord copiées de la table de déclenchement et transférées dans la table supprimée.

Le tableau inséré stocke des copies des nouvelles lignes ou des changements après une INSERT instruction ou.UPDATE Lors de l’exécution d’une INSERT instruction ou, UPDATE les nouvelles lignes ou les lignes modifiées de la table de déclenchement sont copiées dans la table insérée. 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 UPDATE affirmation, la séquence d’événements suivante se produit :

1. La ligne d’origine est copiée de la table du déclencheur vers la table deleted.
2. La table des déclencheurs est mise à jour avec les nouvelles valeurs issues de l’énoncé UPDATE .
3. 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 la référence à la table supprimée lors du test d’un INSERT ou à la table insérée lors du test de un DELETE ne provoque aucune erreur, ces tables de test déclencheurs ne contiennent aucune ligne dans ces cas.

SQL Server n’autorise pas les références aux colonnes de type text, ntextou image dans les tables inserted et deleted 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
        INNER 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;
    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.

Les règles suivantes concernant le moment où une INSERT instruction ou UPDATE référençant une table avec un déclencheur INSTEAD OF doit fournir des valeurs pour les colonnes sont les mêmes que si la table n’avait pas de déclencheur INSTEAD OF :

• Aucune valeur ne peut être spécifiée pour une colonne calculée ou de type de données timestamp .

• Les valeurs ne peuvent pas être spécifiées pour les colonnes possédant une IDENTITY propriété, sauf si IDENTITY_INSERT est ACTIVÉ pour cette table. Lorsque IDENTITY_INSERT est ON, les INSERT états doivent fournir une valeur.

• INSERT les instructions doivent fournir des valeurs pour toutes les colonnes NOT NULL qui n’ont pas de DEFAULT contraintes.

• Pour toutes les colonnes sauf calculées, d’identité ou de timestamp , les valeurs sont optionnelles pour toute colonne qui autorise les nulls, ou toute colonne NOT NULL qui possède une DEFAULT définition.

Lorsqu’une INSERTinstruction , UPDATE, ou DELETE une instruction fait référence à une vue qui possède un déclencheur INSTEAD OF, le Moteur de base de données appelle le déclencheur au lieu d’agir directement contre une 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 l’interrogation ou la jointure avec d’autres tables peuvent conduire à des plans de requête peu optimaux et à des exécutions lentes des requêtes. Veillez à concevoir et à tester soigneusement votre application pour répondre à vos besoins en matière de performances de requête.

Contenu connexe

• Déclencheurs DML