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. 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
(PrimaryKey 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 PrimaryKey, 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.PrimaryKey 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 PrimaryKey and ComputedCol columns. For example:
--A correct INSERT statement that skips the PrimaryKey and ComputedCol columns.
INSERT INTO BaseTable (Color, Material)
VALUES (N'Red', N'Cloth')
--View the results of the INSERT statement.
SELECT PrimaryKey, Color, Material, ComputedCol
FROM BaseTable
--An incorrect statement that tries to supply a value for the
--PrimaryKey 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 PrimaryKey and ComputedCol:
--A correct INSERT statement supplying dummy values for the
--PrimaryKey and ComputedCol columns.
INSERT INTO InsteadView (PrimaryKey, Color, Material, ComputedCol)
VALUES (999, N'Blue', N'Plastic', N'XXXXXX')
--View the results of the INSERT statement.
SELECT PrimaryKey, Color, Material, ComputedCol
FROM InsteadView
The inserted table passed to InsteadTrigger is built with a nonnullable PrimaryKey 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.PrimaryKey or inserted.ComputedCol; therefore, the values are ignored. The row actually inserted into BaseTable has 2 in PrimaryKey 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.
See Also
Other Resources
COLUMNPROPERTY (Transact-SQL)
sp_help (Transact-SQL)