You can define instead of insert triggers on a view to replace the standard action of the insert statement. Usually, this 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, a SQL statement that inserts rows into the view must provide a value for the column. In addition to valid non-null values, an explicit value of null is also accepted for a non-nullable column of the view. view columns allow nulls if the expression defining the view column includes such items as:
References to any base table column that allows nulls
Arithmetic operators
References to functions
CASE with a nullable subexpression
NULLIF
sp_help reports which view columns allow nulls.
An insert statement that references a view with 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
If the instead of insert trigger contains an insert statement against the base table using data in the inserted table, the insert statement must ignore the values for these types of columns by not including them in the select list of the statement.The insert statement against the view can generate dummy values for these columns, but the insert statement in the instead of insert trigger ignores those values and Adaptive Server supplies the correct values.
