Expressions et colonnes calculées dans les déclencheurs INSTEAD OF
La liste de sélection d'une vue peut contenir des expressions autres que des expressions simples composées uniquement d'un nom de colonne. Les déclencheurs INSTEAD OF créés sur ces vues doivent avoir une logique telle qu'il leur soit possible de déterminer correctement, parmi les valeurs spécifiées dans les instructions INSERT et UPDATE, celles qui doivent être appliquées aux colonnes de la table de base. Voici quelques exemples d'expressions de ce type :
- les expressions de vue sans mappage vers une colonne d'une table quelle qu'elle soit, telles qu'une constante ou certains types de fonctions ;
- les expressions de vue avec mappage vers plusieurs colonnes, telles que les expressions complexes qui résultent d'une concaténation de chaînes à partir de deux ou de plusieurs colonnes ;
- les expressions de vue qui transforment la valeur d'une seule colonne de la table de base, comme dans le cas d'une référence à une colonne dans une fonction.
Ces considérations s'appliquent également aux colonnes de vue qui sont des expressions simples faisant référence à une colonne calculée dans une table de base. L'expression qui définit la colonne calculée peut présenter la même forme qu'une expression plus complexe dans la liste de sélection de la vue.
Les vues peuvent contenir dans leur liste de sélection des expressions n'établissant de mappage vers aucune colonne de la table de base. Par exemple :
CREATE VIEW ExpressionView
AS
SELECT *, GETDATE() AS TodaysDate
FROM AdventureWorks.HumanResources.Employee
Bien que la colonne TodaysDate ne soit mappée vers aucune colonne de la table, SQL Server 2005 doit créer une colonne TodaysDate dans la table inserted qu'il transmet à un déclencheur INSTEAD OF défini sur ExpressionView. Toutefois, la colonne inserted.TodaysDate acceptant les valeurs NULL, une instruction INSERT faisant référence à ExpressionView ne doit pas forcément fournir une valeur pour cette colonne. Étant donné que l'expression n'établit de mappage vers aucune colonne de table, le déclencheur peut ignorer toute valeur fournie par l'insertion (INSERT) dans cette colonne.
La même approche doit s'appliquer aux expressions de vue simples faisant référence à des colonnes calculées de tables de base et dont le résultat ne dépend d'aucune autre colonne. Par exemple :
CREATE TABLE ComputedExample
(
PrimaryKey int PRIMARY KEY,
ComputedCol AS SUSER_NAME()
)
Certaines expressions complexes établissent un mappage vers plusieurs colonnes. Par exemple :
CREATE TABLE SampleTable
(
PriKey int,
FirstName nvarchar(20),
LastName nvarchar(30)
)
GO
CREATE VIEW ConcatView
AS
SELECT PriKey, FirstName + ' ' + LastName AS CombinedName
FROM SampleTable
L'expression CombinedName de ConcatView comprend les valeurs concaténées des valeurs FirstName et LastName. Si un déclencheur INSTEAD OF INSERT est défini sur ConcatView, vous devez disposer d'une convention qui établit la façon dont les instructions INSERT fournissent une valeur pour la colonne CombinedName grâce à laquelle le déclencheur détermine la partie de la chaîne à intégrer dans la colonne FirstName et celle qu'il faut placer dans la colonne LastName. Si vous voulez que les instructions INSERT spécifient la valeur de CombinedName suivant la convention 'first_name;last_name', le déclencheur suivant est à même de traiter une instruction INSERT :
CREATE TRIGGER InsteadSample on ConcatView
INSTEAD OF INSERT
AS
BEGIN
INSERT INTO SampleTable
SELECT PriKey,
-- Pull out the first name string.
SUBSTRING(
CombinedName,
1,
(CHARINDEX(';', CombinedName) - 1)
),
-- Pull out the last name string.
SUBSTRING(
CombinedName,
(CHARINDEX(';', CombinedName) + 1),
DATALENGTH(CombinedName) - (CHARINDEX(';', CombinedName) + 1)
)
FROM inserted
END
Une logique similaire est requise pour traiter les colonnes de vue qui sont des expressions simples faisant référence à des colonnes calculées avec des expressions complexes.
Certaines expressions de vue peuvent transformer la valeur d'une colonne de table de base, par exemple en effectuant une opération mathématique ou en utilisant la colonne comme paramètre de fonction. Dans ce cas, la logique du déclencheur INSTEAD OF INSERT peut répondre à deux approches :
La convention peut établir que toutes les instructions INSERT fournissent la valeur brute à placer dans la table de base, et que la logique du déclencheur déplace la valeur de la table inserted vers la table de base.
La convention peut établir que toutes les instructions INSERT fournissent la valeur qui doit normalement être renvoyée par une opération SELECT appliquée à la vue, auquel cas la logique du déclencheur doit inverser l'opération. Par exemple :
CREATE TABLE BaseTable ( PrimaryKey int PRIMARY KEY, ColumnB int, ColumnC decimal(19,3) ) CREATE VIEW SquareView AS SELECT PrimaryKey, ColumnB, -- Square the value of ColumnC SQUARE(ColumnC) AS SquareC FROM BaseTable CREATE TRIGGER SquareTrigger ON SquareView INSTEAD OF INSERT AS BEGIN INSERT INTO BaseTable SELECT PrimaryKey, ColumnB, -- Perform logical inverse of function in view. SQRT(SquareC) FROM inserted END
Pour certaines expressions, telles que les expressions complexes utilisant des opérations mathématiques comme l'addition et la soustraction, il peut être impossible pour les utilisateurs de fournir une valeur qui permette au déclencheur d'élaborer des valeurs sans ambiguïté pour les colonnes de la table de base de destination. Par exemple, si la liste de sélection d'une vue contient l'expression IntColA + IntColB AS AddedColumns, que représente la valeur 10 dans inserted.AddedColumns ? 10 est-il le résultat de 3 + 7, 2 + 8 ou 5 + 5 ? Il est impossible de déterminer seulement à partir de la valeur d'inserted.AddedColumns quelles sont les valeurs à placer dans IntColA et IntColB.
En pareil cas, vous pouvez programmer le déclencheur de telle sorte qu'il utilise d'autres sources d'informations pour déterminer les valeurs à définir dans les colonnes de la table de base. La liste de sélection des vues ayant un déclencheur INSTEAD OF doit contenir suffisamment d'informations pour l'élaboration des valeurs de toutes les colonnes non NULL des tables de base modifiées par le déclencheur. Les données ne doivent pas toutes provenir directement de la table inserted. Dans certains cas, les valeurs de la table inserted peuvent être des valeurs de clé utilisées par le déclencheur pour extraire les données adéquates d'autres tables de base.
Voir aussi
Concepts
Utilisation des déclencheurs INSTEAD OF