Introduction to triggers

A trigger is a special form of stored procedure that is executed automatically when a statement that modifies data is executed. You use triggers whenever referential integrity and other declarative constraints are insufficient. See Ensuring data integrity, and CREATE TABLE statement.

You may want to enforce a more complex form of referential integrity involving more detailed checking, or you may want to enforce checking on new data, but allow legacy data to violate constraints. Another use for triggers is in logging the activity on database tables, independent of the applications using the database.

Note

There are three special statements that triggers do not fire after: LOAD TABLE, TRUNCATE, and WRITETEXT. See LOAD TABLE statement, TRUNCATE statement, and WRITETEXT statement [T-SQL].

Trigger execution permissions

Triggers execute with the permissions of the owner of the associated table or view, not the user ID whose actions cause the trigger to fire. A trigger can modify rows in a table that a user could not modify directly.

You can prevent triggers from being fired by specifying the -gf server option, or by setting the fire_triggers option. See:

Trigger types

SQL Anywhere supports the following trigger types:

BEFORE trigger A BEFORE trigger fires before a triggering action is performed. BEFORE triggers can be defined for tables, but not views.
AFTER trigger An AFTER trigger fires after the triggering action is complete. AFTER triggers can be defined for tables, but not views.
INSTEAD OF trigger An INSTEAD OF trigger is a conditional trigger that fires instead of the triggering action. INSTEAD OF triggers can be defined for tables and views (except materialized views). See INSTEAD OF triggers.

For a full description of the syntax for defining a trigger, see CREATE TRIGGER statement.

Trigger events

Triggers can be defined on one or more of the following triggering events:

Action	Description
INSERT	Invokes the trigger whenever a new row is inserted into the table associated with the trigger.
DELETE	Invokes the trigger whenever a row of the associated table is deleted.
UPDATE	Invokes the trigger whenever a row of the associated table is updated.
UPDATE OF column-list	Invokes the trigger whenever a row of the associated table is updated such that a column in the column-list is modified.

You can write separate triggers for each event that you need to handle or, if you have some shared actions and some actions that depend on the event, you can create a trigger for all events and use an IF statement to distinguish the action taking place. See Trigger operation conditions.

Trigger times

Triggers can be either row-level or statement-level:

A row-level trigger executes once for each row that is changed. Row-level triggers execute BEFORE or AFTER the row is changed.

Column values for the new and old images of the affected row are made available to the trigger via variables.
A statement-level trigger executes after the entire triggering statement is completed. Rows affected by the triggering statement are made available to the trigger via temporary tables representing the new and old images of the rows. SQL Anywhere does not support statement-level BEFORE triggers.

Flexibility in trigger execution time is useful for triggers that rely on referential integrity actions such as cascaded updates or deletes being performed (or not) as they execute.

If an error occurs while a trigger is executing, the operation that fired the trigger fails. INSERT, UPDATE, and DELETE are atomic operations. When they fail, all effects of the statement (including the effects of triggers and any procedures called by triggers) revert to their preoperative state. See Atomic compound statements.

Creating triggers
Executing triggers
Altering triggers
Dropping triggers
Trigger execution permissions
Advanced information on triggers

Discuss this page in DocCommentXchange.