Expected behavior of INSERT into views with Instead Of Insert Triggers

  • Article

The INSTEAD OF INSERT Triggers topic in the documentation describes the following:

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 assertions above which I have marked in yellow are not true. Algebrizer doesn’t enforce such condition anywhere.

The one that I marked in orange is not true either. Actually, if you supply any value other than NULL for a view column which is declared as timestamp in the base table, exception 273 will be raised (Cannot insert an explicit value into a timestamp column. Use INSERT with a column list to exclude the timestamp column, or insert a DEFAULT into the timestamp column.)

I have reported these documentation imprecisions so that this topic gets updated to reflect the actual (and expected) behavior.