Trigger INSTEAD OF INSERT

È possibile definire trigger INSTEAD OF INSERT in una vista o una tabella per sostituire l'azione standard dell'istruzione INSERT. Il trigger INSTEAD OF INSERT viene in genere definito in una vista per inserire dati in una o più tabelle.

Le colonne dell'elenco di selezione della vista ammettono valori Null o diversi da Null. Se una colonna della vista non supporta valori Null, è necessario che i valori per la colonna vengano forniti da un'istruzione INSERT. Le colonne della vista supportano valori Null se nell'espressione che definisce la colonna specifica sono inclusi gli elementi seguenti:

• Riferimenti a qualsiasi colonna di tabella di base che supporta valori Null

• Operatori aritmetici

• Riferimenti a funzioni

• Funzione CASE o COALESCE con una sottoespressione che ammette valori Null

• Funzione NULLIF

È possibile utilizzare la proprietà AllowsNull restituita dalla funzione COLUMNPROPERTY per determinare se una colonna della vista supporti valori Null. Anche la stored procedure sp_help restituisce le colonne della vista che supportano valori Null.

Un'istruzione INSERT che fa riferimento a una vista che include un trigger INSTEAD OF INSERT deve fornire valori per ogni colonna della vista che non supporta valori Null, incluse le colonne della vista che fanno riferimento alle colonne della tabella di base per cui non è possibile specificare i valori di input, ad esempio:

• Colonne calcolate nella tabella di base.

• Colonne Identity della tabella di base per cui l'opzione IDENTITY INSERT è impostata su OFF.

• Colonne di tabella di base con il tipo di dati timestamp.

Se il trigger di vista INSTEAD OF INSERT genera un'istruzione INSERT sulla tabella di base utilizzando i dati inclusi nella tabella inserted, deve ignorare i valori per questi tipi di colonne evitando di includere le colonne nell'elenco di selezione dell'istruzione INSERT. L'istruzione INSERT può generare valori fittizi per questi tipi di colonne.

Ad esempio, un'istruzione INSERT deve specificare un valore per una colonna della vista di cui è stato eseguito il mapping a una colonna Identity o calcolata in una tabella di base, ma può fornire un valore segnaposto. Il trigger INSTEAD OF può ignorare il valore fornito quando crea l'istruzione INSERT tramite cui vengono inseriti i valori nella tabella di base.

Le istruzioni seguenti consentono di creare una tabella, una vista e un trigger tramite cui viene illustrato il processo:

CREATE TABLE BaseTable
  (ID     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 ID, 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.ID and 
  --inserted.ComputedCol.
  INSERT INTO BaseTable
       SELECT Color, Material
       FROM inserted
END;
GO

Un'istruzione INSERT che fa riferimento direttamente a BaseTable non può fornire un valore per le colonne ID e ComputedCol. Ad esempio:

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

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

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

Un'istruzione INSERT, tuttavia, che fa riferimento a InsteadView deve fornire un valore per ID e ComputedCol:

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

La tabella inserted passata a InsteadTrigger viene compilata con una colonna ID e una colonna ComputedCol che non ammettono valori Null. È pertanto necessario che l'istruzione INSERT che fa riferimento alla vista fornisca un valore per tali colonne. I valori 999 e N'XXXXXX' vengono passati a InsteadTrigger, ma l'istruzione INSERT nel trigger non seleziona né inserted.ID, né inserted.ComputedCol. I valori vengono pertanto ignorati. La riga effettivamente inserita in BaseTable contiene 2 in ID e N'BluePlastic' in ComputedCol.

I valori contenuti nella tabella inserted per le colonne calcolate, Identity e timestamp sono diversi da quelli dei trigger INSTEAD OF INSERT specificati nelle tabelle confrontate con un trigger INSTEAD OF specificato nelle viste.

Non è necessario che un'istruzione INSERT che fa riferimento direttamente a una tabella di base fornisca valori per una colonna NOT NULL associata anche a una definizione DEFAULT. Se l'istruzione INSERT non fornisce alcun valore, viene utilizzato il valore predefinito. Se un'espressione semplice in una vista che include un trigger INSTEAD OF INSERT fa riferimento a una colonna NOT NULL con una definizione DEFAULT, tuttavia, qualsiasi istruzione INSERT che fa riferimento alla vista dovrà fornire un valore per la colonna. Questo valore è necessario per compilare la tabella inserted passata al trigger. È necessario definire una convenzione per un valore che consenta di segnalare al trigger che deve essere utilizzato il valore predefinito. La convenzione migliore consiste nel fare in modo che l'istruzione INSERT fornisca il valore predefinito.

La tabella deleted in un trigger INSTEAD OF INSERT è sempre vuota.