INSTEAD OF UPDATE Triggers
INSTEAD OF UPDATE triggers can be defined on a view or table to replace the standard action of the UPDATE statement. Typically, the INSTEAD OF UPDATE trigger is defined on a view to modify data in one or more base tables.
UPDATE statements that reference views with INSTEAD OF UPDATE triggers must supply values for all nonnullable view columns referenced in the SET clause. 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 set to OFF.
- Base table columns with the timestamp data type.
Frequently, when an UPDATE statement that references a table tries to set the value of a computed, identity, or timestamp column, an error is generated because the values for these columns must be determined by SQL Server 2005. These columns must be included in the UPDATE statement to meet the NOT NULL requirement of the column. However, if the UPDATE statement references a view with an INSTEAD OF UPDATE trigger, the logic defined in the trigger can bypass these columns and avoid the error. To do this, the INSTEAD OF UPDATE trigger must not try to update the values for the corresponding columns in the base table. You do this by not including the columns in the SET clause of the UPDATE statement. When a record is processed from the inserted table, the computed, identity, or timestamp column can contain a dummy value to meet the NOT NULL column requirement, but the INSTEAD OF UPDATE trigger ignores those values and the correct values are set by SQL Server.
This solution works because an INSTEAD OF UPDATE trigger does not have to process data from the inserted columns that are not updated. In the inserted table passed to an INSTEAD OF UPDATE trigger, the columns specified in the SET clause follow the same rules as the inserted columns in an INSTEAD OF INSERT trigger. For columns not specified in the SET clause, the inserted table contains the values as they existed before the UPDATE statement was issued. The trigger can test whether a specific column has been updated by using the IF UPDATE(column) clause. For more information, see INSTEAD OF INSERT Triggers.
INSTEAD OF UPDATE triggers should use values supplied for computed, identity, or timestamp columns only in WHERE clause search conditions.
The logic an INSTEAD OF UPDATE trigger on a view should use to process updated values supplied for computed, identity, timestamp, or default columns is the same as the logic applied to inserted values for these column types.
Note
INSTEAD OF UPDATE triggers cannot be defined on a table that has a foreign key defined by using an UPDATE action.