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

SQL Anywhere 10.0.1 » SQL Anywhere Server - SQL Usage » Using Procedures, Triggers, and Batches

Advanced information on user-defined functions Next Page

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.

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.

Trigger types

SQL Anywhere supports the following trigger types:

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:

Flexibility in trigger execution time is particularly 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 back to their pre-operation state. See Atomic compound statements.

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