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.
Colonne de table de base | Valeur dans la table inserted d'un déclencheur INSERT de table | Valeur dans la table inserted d'un déclencheur INSTEAD OF INSERT de vue |
---|---|---|
Est une colonne calculée. |
Expression calculée |
Valeur définie par l'utilisateur ou NULL |
elle a une propriété IDENTITY ; |
0 si IDENTITY_INSERT est désactivée (OFF), valeur spécifiée si IDENTITY_INSERT est activée (ON) |
Valeur définie par l'utilisateur ou NULL |
elle a un type de données timestamp ; |
Zéros binaires si la colonne n'accepte pas de valeur NULL, NULL dans le cas contraire |
Valeur définie par l'utilisateur ou NULL |
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)