INSTEAD OF Triggers

A trigger with the INSTEAD OF clause replaces the corresponding triggering action. It is executed after the corresponding inserted and deleted tables are created, but before any integrity constraint or any other action is performed.

INSTEAD OF triggers can be created on tables as well as on views. When a Transact-SQL statement references a view that has an INSTEAD OF trigger, the database system executes the trigger instead of taking any action against any table. The trigger always uses the information in the inserted and deleted tables built for the view to create any statements needed to build the requested event.

There are certain requirements on column values that are supplied by an INSTEAD OF trigger:

• Values cannot be specified for computed columns.
• Values cannot be specified for columns with the TIMESTAMP data type.
• Values cannot be specified for columns with an IDENTITY property, unless the IDENTITY_INSERT option is set to ON.

These requirements are valid only for INSERT and UPDATE statements that reference a base table. An INSERT statement that references a view that has an INSTEAD OF trigger must supply values for all non-nullable columns of that view. (The same is true for an UPDATE statement: an UPDATE statement that references a view that has an INSTEAD OF trigger must supply values for each view column that does not allow nulls and that is referenced in the SET clause.)

Example 14.5 shows the different behavior during insertion of values for computed columns using a table and its corresponding view.

bo

CREATE VIEW all_orders
	AS SELECT orderid, price, quantity, orderdate, total, shippeddate
		FROM orders;

GO
CREATE TRIGGER tr_orders
	ON all_orders INSTEAD OF INSERT
	AS BEGIN
		INSERT INTO orders
			SELECT orderid, price, quantity, orderdate
				FROM inserted
			ENDCode language: PHP (php)

Example 14.5 uses the orders table from Chapter “Indices” with two computed columns (see Example 10.8 in section “Special Types of Indices“). The all_orders view retrieves all rows from this table. This view is used to specify a value for a view column that maps to a computed column in a base table. That way, an INSTEAD OF trigger can be used, which, in the case of an INSERT statement, is replaced by a batch that inserts the values into the base table via the all_orders view. (An INSERT statement that refers directly to the base table cannot supply a value for a computed column.)