Share via

  • Article

INSTEAD OF INSERT Triggers

INSTEAD OF INSERT triggers can be defined on a view or table to replace the standard action of the INSERT statement. Usually, the INSTEAD OF INSERT trigger is defined on a view to insert data into one or more base tables.

Columns in the view select list can be nullable or not nullable. If a view column does not allow nulls, an INSERT statement must provide values for the column (which can be inferred in the case of an identity column or computed column). View columns allow nulls if the expression defining the view column includes items such as the following:

  • References to any base table column that allows nulls

  • Arithmetic operators

  • References to functions

  • CASE or COALESCE with a nullable subexpression

  • NULLIF

You can use the AllowsNull property reported by the COLUMNPROPERTY function to determine whether a view column allows nulls. The sp_help stored procedure also reports which view columns allow nulls.

An INSERT statement that is referencing a view that has an INSTEAD OF INSERT trigger must supply values for every view column that does not allow nulls. This includes view columns that reference columns in the base table for which input values cannot be specified, such as:

  • Computed columns in the base table.

  • Identity columns in the base table for which IDENTITY INSERT is OFF.

  • Base table columns with the timestamp data type.

If the INSTEAD OF INSERT view trigger generates an INSERT against the base table using the data in the inserted table, it must ignore the values for these types of columns by not including the columns in the select list of the INSERT statement. The INSERT statement can generate dummy values for these types of columns.

For example, while an INSERT statement must specify a value for a view column that maps to an identity or computed column in a base table, it can supply a placeholder value. The INSTEAD OF trigger can ignore the value supplied when it forms the INSERT statement that inserts the values into the base table.

The following statements create a table, view, and trigger that illustrate the process:

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

An INSERT statement that refers directly to BaseTable cannot supply a value for the ID and ComputedCol columns. For example:

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

However, an INSERT statement that refers to InsteadView must supply a value for ID and 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;

The inserted table passed to InsteadTrigger is built with a nonnullable ID and ComputedCol column; therefore, the INSERT statement that is referencing the view must supply a value for those columns. The values 999 and N'XXXXXX' are passed in to InsteadTrigger, but the INSERT statement in the trigger does not select either inserted.ID or inserted.ComputedCol; therefore, the values are ignored. The row actually inserted into BaseTable has 2 in ID and N'BluePlastic' in ComputedCol.

The values contained in the inserted table for computed, identity, and timestamp columns are different for INSTEAD OF INSERT triggers specified on tables compared to an INSTEAD OF triggers specified on views.

Base table column

Value in inserted table in any INSERT trigger on a table

Value in inserted table in an INSTEAD OF INSERT trigger on a view

Is a computed column.

Computed expression

User-specified value or NULL

Has an IDENTITY property.

0 if IDENTITY_INSERT is OFF, specified value if IDENTITY_INSERT is ON

User-specified value or NULL

Has a timestamp data type.

Binary zeros if the column does not allow nulls, NULL if column allows nulls

User-specified value or NULL

An INSERT statement that directly references a base table does not have to supply values for a NOT NULL column that also has a DEFAULT definition. If the INSERT statement does not supply a value, the default value is used. If a NOT NULL column with a DEFAULT definition is referenced by a simple expression in a view that has an INSTEAD OF INSERT trigger, however, any INSERT statement referencing the view must supply a value for the column. This value is required to build the inserted table passed to the trigger. A convention is required for a value that signals to the trigger that the default value should be used. The best convention is for the INSERT statement to supply the default value.

The deleted table in an INSTEAD OF INSERT trigger is always empty.