Click here to view and discuss this page in DocCommentXchange. In the future, you will be sent there automatically.

SQL Anywhere 12.0.1 » SQL Anywhere Server - SQL Usage » Stored procedures, triggers, batches, and user defined functions » Triggers » Advanced information on triggers


INSTEAD OF triggers

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.

  • Whether an INSTEAD OF trigger performs recursion 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 on 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.

 Updating non-updatable views using INSTEAD OF triggers
 See also