ASA SQL User's Guide
Using Procedures, Triggers, and Batches
A trigger is a special form of stored procedure that is executed automatically when a query that modifies data is executed. You use triggers whenever referential integrity and other declarative constraints are insufficient.
For more information on referential integrity, 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 permissionsTriggers execute with the permissions of the owner of the associated table, 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. |
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 has been modified |
You may 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.
For more information, see Trigger operation conditions.
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.
A statement-level trigger executes after the entire triggering statement is completed.
Flexibility in trigger execution time is particularly useful for triggers that rely on referential integrity actions such as cascaded updates or deletes being carried out (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 (see Atomic compound statements). 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.
For a full description of trigger syntax, see CREATE TRIGGER statement.
Creating triggers
Executing triggers
Altering triggers
Dropping triggers
Trigger execution permissions