Déclencheurs INSTEAD OF INSERT

Les déclencheurs INSTEAD OF INSERT peuvent être définis sur une vue ou sur une table pour remplacer l'action standard de l'instruction INSERT. Les déclencheurs INSTEAD OF INSERT sont généralement définis sur une vue pour insérer des données dans une ou plusieurs tables de base.

Les colonnes figurant dans la liste de sélection de la vue peuvent accepter ou refuser les valeurs NULL. Si une colonne de la vue n'accepte pas de valeur NULL, une instruction INSERT doit lui fournir des valeurs. Les colonnes d'une vue autorisent les valeurs NULL si l'expression qui les définit comprend des éléments tels que :

• des références à une colonne de table de base qui accepte les valeurs NULL ;
• des opérateurs arithmétiques ;
• des références à des fonctions ;
• une fonction CASE ou COALESCE avec une sous-expression acceptant les valeurs NULL ;
• NULLIF.

Vous pouvez utiliser la propriété AllowsNull indiquée par la fonction COLUMNPROPERTY pour déterminer si une colonne de vue accepte les valeurs NULL. La procédure stockée sp_help indique également les colonnes de vue qui acceptent les valeurs NULL.

Une instruction INSERT faisant référence à une vue sur laquelle est défini un déclencheur INSTEAD OF INSERT doit fournir des valeurs pour toutes les colonnes de vue qui n'acceptent pas les valeurs NULL et notamment celles qui font référence à des colonnes de la table de base pour lesquelles aucune valeur d'entrée ne peut être spécifiée :

• colonnes calculées de la table de base ;
• colonnes identité de la table de base pour lesquelles IDENTITY_INSERT est désactivée (OFF) ;
• colonnes de la table de base présentant le type de données timestamp.

Si le déclencheur INSTEAD OF INSERT de la vue génère une instruction INSERT sur la table de base à l'aide des données de la table inserted, il doit ignorer les valeurs correspondant à ces types de colonnes et ne pas inclure les colonnes dans la liste de sélection de l'instruction INSERT. L'instruction INSERT peut générer des valeurs factices pour ces types de colonnes.

Par exemple, lorsqu'une instruction INSERT doit spécifier une valeur pour une colonne de vue qui correspond à une colonne d'identité ou une colonne calculée de la table de base, elle peut fournir une valeur d'espace réservé. Le déclencheur INSTEAD OF peut ignorer la valeur fournie lorsqu'il élabore l'instruction INSERT qui insère les valeurs dans la table de base.

Les instructions suivantes illustrent ce processus au travers de la création d'une table, d'une vue et d'un déclencheur :

CREATE TABLE BaseTable
  (PrimaryKey     int PRIMARY KEY IDENTITY(1,1),
   Color          nvarchar(10) NOT NULL,
   Material       nvarchar(10) NOT NULL,
   ComputedCol AS (Color + Material)
  )
GO

--Create a view that contains all columns from the base table.
CREATE VIEW InsteadView
AS SELECT PrimaryKey, Color, Material, ComputedCol
FROM BaseTable
GO

--Create an INSTEAD OF INSERT trigger on the view.
CREATE TRIGGER InsteadTrigger on InsteadView
INSTEAD OF INSERT
AS
BEGIN
  --Build an INSERT statement ignoring inserted.PrimaryKey and 
  --inserted.ComputedCol.
  INSERT INTO BaseTable
       SELECT Color, Material
       FROM inserted
END
GO

Une instruction INSERT qui fait directement référence à BaseTable ne peut pas fournir de valeur pour les colonnes PrimaryKey et ComputedCol. Exemple :

--A correct INSERT statement that skips the PrimaryKey and ComputedCol columns.
INSERT INTO BaseTable (Color, Material)
       VALUES (N'Red', N'Cloth')

--View the results of the INSERT statement.
SELECT PrimaryKey, Color, Material, ComputedCol
FROM BaseTable

--An incorrect statement that tries to supply a value for the 
--PrimaryKey and ComputedCol columns.
INSERT INTO BaseTable
       VALUES (2, N'Green', N'Wood', N'GreenWood')

Une instruction INSERT qui fait référence à InsteadView doit toutefois fournir une valeur pour les colonnes PrimaryKey et ComputedCol :

--A correct INSERT statement supplying dummy values for the 
--PrimaryKey and ComputedCol columns.
INSERT INTO InsteadView (PrimaryKey, Color, Material, ComputedCol)
       VALUES (999, N'Blue', N'Plastic', N'XXXXXX')
--View the results of the INSERT statement.
SELECT PrimaryKey, Color, Material, ComputedCol
FROM InsteadView

La table inserted transmise à InsteadTrigger est conçue avec des colonnes PrimaryKey et ComputedCol n'acceptant pas les valeurs NULL. L'instruction INSERT qui fait référence à la vue doit donc fournir une valeur pour ces colonnes. Les valeurs 999 et N'XXXXXX' sont transmises à InsteadTrigger mais l'instruction INSERT du déclencheur ne sélectionne pas inserted.PrimaryKey ou inserted.ComputedCol. Les valeurs sont donc ignorées. La ligne insérée dans BaseTable indique 2 dans la colonne PrimaryKey et N'BluePlastic' dans la colonne ComputedCol.

Les valeurs contenues dans la table inserted pour les colonnes calculées, d'identité et timestamp diffèrent selon que vous appliquez des déclencheurs INSTEAD OF INSERT spécifiés sur des tables ou sur des vues.

Une instruction INSERT qui fait directement référence à une table de base n'a pas besoin de fournir de valeurs pour une colonne NOT NULL dotée d'une valeur par défaut (DEFAULT). Si elle ne fournit pas de valeur, la valeur par défaut (DEFAULT) est utilisée. Toutefois, si une colonne NOT NULL possédant une valeur par défaut (DEFAULT) est référencée par une expression simple dans une vue à laquelle est associé un déclencheur INSTEAD OF INSERT, toute instruction INSERT faisant référence à la vue doit fournir une valeur pour la colonne. Cette valeur est requise pour créer la table inserted transmise au déclencheur. Une convention doit être adoptée quant à la valeur indiquant au déclencheur que la valeur par défaut doit être utilisée. La meilleure convention consiste à ce que l'instruction INSERT fournisse la valeur par défaut.

La table deleted figurant dans un déclencheur INSTEAD OF INSERT est toujours vide.

Voir aussi

Autres ressources

COLUMNPROPERTY (Transact-SQL)
sp_help (Transact-SQL)

Aide et Informations

Assistance sur SQL Server 2005