INSTEAD OF triggers differ from BEFORE and AFTER triggers because when an INSTEAD OF trigger fires, the triggering action is skipped and the specified action is performed instead.
The following is a list of capabilities and restrictions that are unique to INSTEAD OF triggers:
There can only be one INSTEAD OF trigger for each trigger event on a given table.
INSTEAD OF triggers can be defined for a table or a view. However, INSTEAD OF triggers cannot be defined on materialized views since you cannot execute DML operations, such as INSERT, DELETE, and UPDATE statements, on materialized views.
You cannot specify the ORDER or WHEN clauses when defining an INSTEAD OF trigger.
You cannot define an INSTEAD OF trigger for an UPDATE OF column-list trigger event. See CREATE TRIGGER statement.
Whether or not an INSTEAD OF trigger recurses depends on whether the target of the trigger is a base table or a view. Recursion occurs for views, but not for base tables. That is, if an INSTEAD OF trigger performs DML operations on the base table on which the trigger is defined, those operations do not cause triggers to fire (including BEFORE or AFTER triggers). If the target is a view, all triggers fire for the operations performed on the view.
If a table has an INSTEAD OF trigger defined on it, you cannot execute an INSERT statement with an ON EXISTING clause against the table. Attempting to do so returns a SQLE_INSTEAD_TRIGGER error.
You cannot execute an INSERT statement against a view that was defined with the WITH CHECK OPTION (or is nested inside another view that was defined this way), and that has an INSTEAD OF INSERT trigger defined against it. This is true for UPDATE and DELETE statements as well. Attempting to do so returns a SQLE_CHECK_TRIGGER_CONFLICT error.
If an INSTEAD OF trigger is fired as a result of a positioned update, positioned delete, PUT statement, or wide insert operation, a SQLE_INSTEAD_TRIGGER_POSITIONED error is returned.
INSTEAD OF triggers allow you to execute INSERT, UPDATE, or DELETE statements against a view that is not inherently updatable. The body of the trigger defines what it means to execute the corresponding statement. For example, suppose you create the following view:
CREATE VIEW V1 ( Surname, GivenName, State ) AS SELECT DISTINCT Surname, GivenName, State FROM Contacts;
You cannot delete rows from V1 because the DISTINCT keyword makes V1 not inherently updatable. In other words, the database server cannot unambiguously determine what it means to delete a row from V1. However, you could define an INSTEAD OF DELETE trigger that implements a delete operation on V1. For example, the following trigger deletes all rows from Contacts with a given Surname, GivenName, and State when that row is deleted from V1:
CREATE TRIGGER V1_Delete INSTEAD OF DELETE ON V1 REFERENCING OLD AS old_row FOR EACH ROW BEGIN DELETE FROM Contacts WHERE Surname = old_row.Surname AND GivenName = old_row.GivenName AND State = old_row.State END;
Once the V1_Delete trigger is defined, it becomes possible to delete rows from V1. Other INSTEAD OF triggers could be defined to allow INSERT and UPDATE statements to be performed on V1, as well.
If a view with an INSTEAD OF DELETE trigger is nested in another view, it will be treated like a base table for the purposes of checking updatability for a DELETE. The same is true for INSERT and UPDATE. Continuing from our previous example, suppose you create another view:
CREATE VIEW V2 ( Surname, GivenName ) AS SELECT Surname, GivenName from V1;
Without the V1_Delete trigger, it is not possible to delete rows from V2. This is because V1 is not inherently updatable, so neither is V2. However, with an INSTEAD OF DELETE trigger defined on V1, it becomes possible to delete rows from V2. Each row deleted from V2 results in a row being deleted from V1, which causes the V1_Delete trigger to fire.
It is important to be aware of any INSTEAD OF triggers defined on nested views, since the firing of those triggers may have unintended consequences. To make the intended behavior explicit, it is considered good practice in cases like these to define INSTEAD OF triggers on any view referencing the nested view. In our example, the following trigger could be defined on V2 to implement the desired behavior for a DELETE statement:
CREATE TRIGGER V2_Delete INSTEAD OF DELETE ON V2 REFERENCING OLD AS old_row FOR EACH ROW BEGIN DELETE FROM Contacts WHERE Surname = old_row.Surname AND GivenName = old_row.GivenName END;
The V2_Delete trigger ensures that the behavior of a DELETE on V2 remains the same, even if the INSTEAD OF DELETE trigger on V1 is removed or changed.