INSTEAD OF INSERT-Trigger

Mithilfe von INSTEAD OF INSERT-Triggern für eine Sicht oder eine Tabelle kann die Standardaktion der INSERT-Anweisung ersetzt werden. Normalerweise wird der INSTEAD OF INSERT-Trigger für eine Sicht definiert, um Daten in mindestens eine Basistabelle einzufügen.

Die Spalten in der Auswahlliste der Sicht können Spalten sein, die NULL-Werte zulassen oder die keine NULL-Werte zulassen. Wenn eine Sichtspalte keine NULL-Werte zulässt, muss eine INSERT-Anweisung Werte für die Spalte bereitstellen. Sichtspalten lassen NULL-Werte zu, wenn der Ausdruck, der die Sichtspalte definiert, die folgenden Elemente enthält:

• Verweise auf Basistabellenspalten, die NULL-Werte zulassen

• Arithmetische Operatoren

• Verweise auf Funktionen

• CASE oder COALESCE mit einem untergeordneten Ausdruck, der NULL-Werte zulässt

• NULLIF

Mithilfe der von der COLUMNPROPERTY-Funktion gemeldeten AllowsNull-Eigenschaft können Sie bestimmen, ob eine Sichtspalte NULL-Werte zulässt. Die gespeicherte sp_help-Prozedur meldet ebenfalls, welche Sichtspalten NULL-Werte zulassen.

Eine INSERT-Anweisung, die auf eine Sicht verweist, die einen INSTEAD OF INSERT-Trigger aufweist, muss für jede Sichtspalte, die keine NULL-Werte zulässt, Werte angeben. Dazu zählen Sichtspalten, die auf Spalten in der Basistabelle verweisen, für die keine Eingabewerte angegeben werden können, wie z. B.:

• Berechnete Spalten in der Basistabelle.

• Identitätsspalten in der Basistabelle, für die IDENTITY INSERT auf OFF festgelegt ist.

• Basistabellenspalten vom timestamp-Datentyp.

Falls der INSTEAD OF INSERT-Sichttrigger mit den Daten in der inserted-Tabelle eine INSERT-Anweisung für die Basistabelle generiert, müssen die Werte für diese Spaltentypen ignoriert werden, ausgenommen die Spalten in der Auswahlliste der INSERT-Anweisung. Die INSERT-Anweisung kann Pseudowerte für diese Spaltentypen generieren.

Zwar muss z. B. eine INSERT-Anweisung einen Wert für eine Sichtspalte angeben, die einer Identitätsspalte oder einer berechneten Spalte in einer Basistabelle zugeordnet ist, sie kann jedoch einen Platzhalterwert angeben. Der INSTEAD OF-Trigger kann den eingegebenen Wert beim Erstellen der INSERT-Anweisung ignorieren, die die Werte in die Basistabelle einfügt.

Die folgenden Anweisungen erstellen eine Tabelle, eine Sicht und einen Trigger, mit denen dieser Prozess veranschaulicht wird:

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

Eine INSERT-Anweisung, die direkt auf BaseTable verweist, kann keinen Wert für die Spalten ID und ComputedCol angeben. Beispiel:

--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');

Jedoch muss eine INSERT-Anweisung, die auf InsteadView verweist, einen Wert für ID und ComputedCol angeben:

--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;

Die an InsteadTrigger übergebene inserted-Tabelle wird mit den ID- und ComputedCol-Spalten erstellt, die keine NULL-Werte zulassen. Aus diesem Grund muss die INSERT-Anweisung, die auf die Sicht verweist, einen Wert für diese Spalten angeben. Die Werte 999 und N'XXXXXX' werden an InsteadTrigger übergeben, aber die INSERT-Anweisung im Trigger wählt weder inserted.ID noch inserted.ComputedCol aus. Daher werden die Werte ignoriert. Die Zeile, die tatsächlich in BaseTable eingefügt wird, enthält 2 in ID und N'BluePlastic' in ComputedCol.

Die Werte, die in der inserted-Tabelle für berechnete Spalten, Identitätsspalten und timestamp-Spalten enthalten sind, sind für INSTEAD OF INSERT-Trigger, die für Tabellen angegeben sind, und für INSTEAD OF-Trigger, die für Sichten angegeben sind, unterschiedlich.

Eine INSERT-Anweisung, die direkt auf eine Basistabelle verweist, muss keine Werte für eine NOT NULL-Spalte angeben, die auch eine DEFAULT-Definition aufweist. Falls die INSERT-Anweisung keinen Wert angibt, wird der Standardwert verwendet. Verweist jedoch ein einfacher Ausdruck in einer Sicht, die einen INSTEAD OF INSERT-Trigger enthält, auf eine NOT NULL-Spalte mit einer DEFAULT-Definition, muss jede INSERT-Anweisung, die auf diese Sicht verweist, einen Wert für die Spalte angeben. Dieser Wert ist erforderlich, um die inserted-Tabelle zu erstellen, die an den Trigger übergeben wird. Für einen Wert, der dem Trigger signalisiert, dass der Standardwert verwendet werden soll, ist eine Konvention erforderlich. Die optimale Konvention ist, dass die INSERT-Anweisung den Standardwert angibt.

Die deleted-Tabelle in einem INSTEAD OF INSERT-Trigger ist immer leer.