Espressioni e colonne calcolate nei trigger INSTEAD OF
L'elenco di selezione di una vista può contenere espressioni diverse da semplici espressioni costituite solo dal nome di una colonna. La logica dei trigger INSTEAD OF presenti in queste viste deve consentire di determinare correttamente i valori da impostare nella tabella di base tramite i valori specificati nelle istruzioni INSERT e UPDATE. Di seguito sono riportati alcuni esempi di espressioni di questo tipo:
Espressioni di vista che non eseguono il mapping a colonne di tabella, ad esempio una costante o alcuni tipi di funzione.
Espressioni di vista che eseguono il mapping a più colonne, ad esempio espressioni complesse formate dalla concatenazione delle stringhe di due o più colonne.
Espressioni di vista che trasformano il valore di un'unica colonna della tabella di base, ad esempio una funzione contenente il riferimento a una colonna.
Ciò si applica anche alle colonne della vista che rappresentano espressioni semplici facenti riferimento a una colonna calcolata di una tabella di base. L'espressione che definisce la colonna calcolata può essere nello stesso formato di un'espressione più complessa nell'elenco di selezione della vista.
Le viste possono contenere espressioni che non eseguono il mapping a colonne della tabella chiave, ad esempio:
CREATE VIEW dbo.ExpressionView
AS
SELECT BusinessEntityID, JobTitle, GETDATE() AS TodaysDate
FROM AdventureWorks2008R2.HumanResources.Employee;
Sebbene la colonna TodaysDate non esegua il mapping a colonne della tabella, è necessario che SQL Server compili una colonna TodaysDate nella tabella inserted che passa a un trigger INSTEAD OF definito in ExpressionView. La colonna inserted.TodaysDate tuttavia ammette valori Null, pertanto non è necessario che un'istruzione INSERT che fa riferimento a ExpressionView fornisca un valore per questa colonna. Poiché l'espressione non esegue il mapping a una colonna di una tabella, il trigger può ignorare qualsiasi valore fornito da INSERT in questa colonna.
Lo stesso approccio va applicato alle espressioni di vista semplici facenti riferimento a colonne calcolate in tabelle di base che compilano un risultato non dipendente da altre colonne, ad esempio:
CREATE TABLE dbo.ComputedExample
(
PrimaryKey int PRIMARY KEY,
ComputedCol AS SUSER_NAME()
);
Alcune espressioni complesse eseguono il mapping a più colonne, ad esempio:
CREATE TABLE dbo.SampleTable
(
PriKey int,
FirstName nvarchar(20),
LastName nvarchar(30)
);
GO
CREATE VIEW dbo.ConcatView
AS
SELECT PriKey, FirstName + ' ' + LastName AS CombinedName
FROM SampleTable;
L'espressione CombinedName in ConcatView contiene i valori concatenati dei valori FirstName e LastName. Se si definisce un trigger INSTEAD OF INSERT in ConcatView è necessaria una convenzione relativa al modo in cui le istruzioni INSERT forniscono un valore per la colonna CombinedName che consenta al trigger di determinare la parte di stringa da inserire nella colonna FirstName e la parte da inserire nella colonna LastName. Se si stabilisce che le istruzioni INSERT specifichino il valore di CombinedName utilizzando la convenzione 'first_name;last_name', il trigger seguente potrà elaborare correttamente un'istruzione INSERT:
CREATE TRIGGER InsteadSample on dbo.ConcatView
INSTEAD OF INSERT
AS
BEGIN
INSERT INTO dbo.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;
Una logica simile è necessaria per l'elaborazione delle colonne della vista costituite da espressioni semplici facenti riferimento a colonne calcolate con espressioni complesse.
Alcune espressioni di vista possono trasformare il valore di una colonna della tabella di base, ad esempio eseguendo un'operazione matematica oppure utilizzando la colonna come parametro per una funzione. In questo caso, la logica nel trigger INSTEAD OF INSERT può applicare due approcci:
Utilizzare la convenzione che che tutte le istruzioni INSERT forniscano il valore non elaborato da inserire nella tabella di base e che il trigger sposti il valore dalla tabella inserted alla tabella di base.
Utilizzare la convenzione che tutte le istruzioni INSERT forniscano il valore restituito da un'istruzione SELECT sulla vista. In questo caso la logica del trigger deve invertire l'operazione. Ad esempio:
CREATE TABLE dbo.BaseTable ( PrimaryKey int PRIMARY KEY, ColumnB int, ColumnC decimal(19,3) ); CREATE VIEW dbo.SquareView AS SELECT PrimaryKey, ColumnB, -- Square the value of ColumnC SQUARE(ColumnC) AS SquareC FROM BaseTable; CREATE TRIGGER SquareTrigger ON dbo.SquareView INSTEAD OF INSERT AS BEGIN INSERT INTO dbo.BaseTable SELECT PrimaryKey, ColumnB, -- Perform logical inverse of function in view. SQRT(SquareC) FROM inserted END;
Per alcune espressioni, ad esempio espressioni complesse che utilizzano operazioni matematiche come addizione e sottrazione, è possibile che l'utente non sia in grado di fornire un valore che il trigger possa utilizzare per compilare valori per le colonne della tabella di base di destinazione in modo non ambiguo. Ad esempio, se l'elenco di selezione di una vista contiene l'espressione IntColA + IntColB AS AddedColumns, un valore 10 nella colonna inserted.AddedColumns può indicare il risultato di 3 + 7, 2 + 8, oppure 5 + 5. Non è possibile stabilire dal solo valore di inserted.AddedColumns quali siano i valori da inserire in IntColA e IntColB.
In questi casi è possibile scrivere il codice del trigger in modo che utilizzi fonti di informazione alternative per determinare i valori da impostare nelle colonne della tabella di base. Per le viste che contengono trigger INSTEAD OF, l'elenco di selezione della vista deve contenere informazioni sufficienti alla compilazione di valori per tutte le colonne non Null delle tabelle di base modificate dal trigger. Non è necessario che tutti i dati provengano direttamente dalla tabella inserted. In alcuni casi i valori presenti nella colonna inserted possono essere valori chiave utilizzati dal trigger per recuperare i dati rilevanti da altre tabelle di base.