Utiliser les tables insérées et supprimées

Les instructions de déclencheur DML utilisent deux tables spéciales : la table supprimée et les tables insérées. SQL Server crée et gère automatiquement ces tables. Vous pouvez utiliser ces tables temporaires résidentes en mémoire pour 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 des tables ou effectuer des opérations de langage de définition de données (DDL) sur les tables, telles que CREATE INDEX.

Dans les déclencheurs DML, les tables insérées et supprimées sont principalement utilisées pour effectuer les opérations suivantes :

• Étendre l’intégrité référentielle entre les tables.

• Insérez ou mettez à jour des données dans des tables de base sous-jacentes à une vue.

• Testez les erreurs et effectuez des actions en fonction de l’erreur.

• Recherchez la différence entre l’état d’une table avant et après une modification de données et effectuez des actions en fonction de cette différence.

La table supprimée stocke des copies des lignes affectées pendant les instructions DELETE et UPDATE. Pendant l’exécution d’une instruction DELETE ou UPDATE, les lignes sont supprimées de la table de déclencheurs et transférées vers la table supprimée. La table supprimée et la table de déclencheurs n’ont généralement aucune ligne en commun.

La table insérée stocke les copies des lignes affectées pendant les instructions INSERT et UPDATE. Pendant une transaction d’insertion ou de mise à jour, de nouvelles lignes sont ajoutées à la table insérée et à la table de déclencheurs. Les lignes de la table insérée sont des copies des nouvelles lignes de la table de déclenchement.

Une transaction de mise à jour est similaire à une opération de suppression suivie d’une opération d’insertion ; les anciennes lignes sont d’abord copiées dans la table supprimée, puis les nouvelles lignes sont copiées dans la table de déclencheurs et dans la table insérée.

Lorsque vous définissez des conditions de déclencheur, utilisez les tables insérées et supprimées de manière appropriée pour l’action qui a déclenché le déclencheur. Bien que le référencement de la table supprimée lors du test d’une table INSERT ou de la table insérée lors du test d’une suppression ne provoque aucune erreur, ces tables de test de déclencheur ne contiennent aucune ligne dans ces cas.

SQL Server 2014 n'autorise pas les références de colonne text, ntext ou image dans les tables 'inserted' et 'deleted' pour les déclencheurs AFTER. Toutefois, ces types de données sont inclus uniquement à des fins de compatibilité descendante. Le stockage préféré pour les données volumineuses consiste à 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 insérées et supprimées. Pour plus d’informations, consultez CREATE TRIGGER (Transact-SQL).

Exemple d’utilisation de la table insérée dans un déclencheur pour appliquer des règles d’entreprise

Étant donné que les contraintes CHECK peuvent référencer uniquement les colonnes sur lesquelles la contrainte au niveau des colonnes ou au niveau de la table est définie, toutes les contraintes inter-tables (dans ce cas, les règles métier) doivent être définies en tant que déclencheurs.

L’exemple suivant crée un déclencheur DML. Ce déclencheur vérifie que la cote de crédit pour le fournisseur est bonne lorsqu’une tentative d’insertion d’une nouvelle commande dans la PurchaseOrderHeader table est effectuée. Pour obtenir la cote de crédit du fournisseur correspondant à la commande d’achat qui vient d’être insérée, la Vendor table doit être référencée et jointe à la table insérée. Si la cote de crédit est trop faible, un message s’affiche et l’insertion ne s’exécute pas. Notez que cet exemple n’autorise pas les modifications de données multirow. Pour plus d’informations, consultez Créer des déclencheurs DML pour gérer plusieurs lignes de données.

USE AdventureWorks2012;
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 EXISTS (SELECT *
           FROM Purchasing.PurchaseOrderHeader p 
           JOIN inserted AS i 
           ON p.PurchaseOrderID = i.PurchaseOrderID 
           JOIN Purchasing.Vendor AS v 
           ON v.BusinessEntityID = p.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

Utilisation des tableaux insérés et supprimés dans les déclencheurs INSTEAD OF

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

Les règles suivantes concernant l’utilisation d’une instruction INSERT ou UPDATE faisant référence à une table avec un déclencheur INSTEAD OF doivent fournir des valeurs pour les colonnes comme si la table n’avait pas de déclencheur INSTEAD OF :

• Les valeurs ne peuvent pas être spécifiées pour les colonnes calculées ou les colonnes avec un timestamp type de données.

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

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

• Pour toutes les colonnes, à l'exception des colonnes calculées, d’identité ou timestamp, les valeurs sont facultatives pour toute colonne qui autorise les valeurs nulles ou toute colonne NOT NULL qui a une définition DEFAULT.

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 insérées et supprimées pour générer toutes les instructions requises pour implémenter l’action demandée dans les tables de base, même lorsque le format des informations dans les tables insérées et supprimées créées pour la vue est différent du format des données dans les tables de base.

Le format des tables insérées et supprimées 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 AdventureWorks2012;  
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 de cette vue comporte trois colonnes : une colonne int et deux colonnes nvarchar. Les tables insérées et supprimées passées à un déclencheur INSTEAD OF défini sur la vue ont également une int colonne nommée BusinessEntityID, une nvarchar colonne nommée LName, et une nvarchar colonne nommée FName.

La liste de sélection d’une vue peut également contenir des expressions qui ne correspondent pas directement à une seule colonne de table de base. Certaines expressions d’affichage, telles qu’une constante ou une invocation de fonction, peuvent ne pas référencer de colonnes et peuvent être ignorées. Les expressions complexes peuvent référencer plusieurs colonnes, mais les tables insérées et supprimées n’ont qu’une seule valeur pour chaque ligne insérée. Les mêmes problèmes s’appliquent aux expressions simples dans une vue si elles font référence à une colonne calculée qui a une expression complexe. Un déclencheur INSTEAD OF sur la vue doit gérer ces types d’expressions.