Collection Contents Index Introduction to user-defined functions Introduction to batches pdf/chap10.pdf

User's Guide
   PART 1. Working with Databases
     CHAPTER 10. Using Procedures, Triggers, and Batches       

Introduction to triggers


Triggers are used whenever referential integrity and other declarative constraints are not sufficient.

For Info     For 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 permissions
    Triggers 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 actions:

Action

Description

INSERT

The trigger is invoked whenever a new row is inserted into the table associated with the trigger

DELETE

The trigger is invoked whenever a row of the associated table is deleted.

UPDATE

The trigger is invoked whenever a row of the associated table is updated.

UPDATE OF column-list

The trigger is invoked whenever a row of the associated table is updated such that a column in the column-list has been modified

Triggers can be defined as row-level triggers or statement-level triggers. Row-level triggers can execute BEFORE or AFTER each row modified by the triggering insert, update, or delete operation is changed. Statement-level triggers execute after the entire operation is performed.

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) are undone.

Top of page  Creating triggers

You create triggers using the CREATE TRIGGER statement. You must have RESOURCE authority in order to create a trigger and you must have ALTER permissions on the table associated with the trigger. For information about ALTER permissions, see Granting permissions on tables and views. For information about RESOURCE permissions, see Granting DBA and resource authority.

The body of a trigger consists of a compound statement (see Using compound statements): a set of semicolon-delimited SQL statements bracketed by a BEGIN and an END statement.

COMMIT and ROLLBACK and some ROLLBACK TO SAVEPOINT statements are not permitted within a trigger (see Transactions and savepoints in procedures and triggers).

A row-level INSERT trigger example 

The following trigger is an example of a row-level INSERT trigger. It checks that the birthdate entered for a new employee is reasonable:

CREATE TRIGGER check_birth_date 
   AFTER INSERT ON Employee
REFERENCING NEW AS new_employee
FOR EACH ROW
BEGIN
   DECLARE err_user_error EXCEPTION 
   FOR SQLSTATE '99999';
   IF new_employee.birth_date > 'June 6, 1994' THEN
      SIGNAL err_user_error;
   END IF;
END

This trigger is fired just after any row is inserted into the employee table. It detects and disallows any new rows that correspond to birth dates later than June 6, 1994.

The phrase REFERENCING NEW AS new_employee allows statements in the trigger code to refer to the data in the new row using the alias new_employee.

Signaling an error causes the triggering statement as well as any previous effects of the trigger to be undone.

For an INSERT statement that adds many rows to the employee table, the check_birth_date trigger is fired once for each new row. If the trigger fails for any of the rows, all effects of the INSERT statement are rolled back.

You can specify that the trigger fire before the row is inserted rather than after by changing the first line of the example to:

CREATE TRIGGER mytrigger BEFORE INSERT ON Employee

The REFERENCING NEW clause refers to the inserted values of the row; it is independent of the timing (BEFORE or AFTER) of the trigger.

A row-level DELETE trigger example 

The following CREATE TRIGGER statement defines a row-level DELETE trigger:

CREATE TRIGGER mytrigger BEFORE DELETE ON employee
REFERENCING OLD AS oldtable
FOR EACH ROW
BEGIN
   ...
END

The REFERENCING OLD clause enables the delete trigger code to refer to the values in the row being deleted using the alias oldtable.

You can specify that the trigger fire after the row is deleted rather than before, by changing the first line of the example to:

CREATE TRIGGER mytrigger AFTER DELETE ON employee

The REFERENCING OLD clause is independent of the timing (BEFORE or AFTER) of the trigger.

A statement-level UPDATE trigger example 

The following CREATE TRIGGER statement is appropriate for statement-level UPDATE triggers:

CREATE TRIGGER mytrigger AFTER UPDATE ON employee
REFERENCING NEW AS table_after_update
            OLD AS table_before_update
FOR EACH STATEMENT
BEGIN
   ...
END

The REFERENCING NEW and REFERENCING OLD clause allows the UPDATE trigger code to refer to both the old and new values of the rows being updated. Columns in the new row are referred to with the table alias table_after_update and columns in the old row are referred to with the table alias table_before_update.

The REFERENCING NEW and REFERENCING OLD clause has a slightly different meaning for statement-level and row-level triggers. For statement-level triggers the REFERENCING OLD or NEW aliases are table aliases, while in row-level triggers they refer to the row being altered.

Top of page  Executing triggers

Triggers are executed automatically whenever an INSERT, UPDATE, or DELETE operation is performed on the table named in the trigger. A row-level trigger is fired once for each row that is affected, while a statement-level trigger is fired once for the entire statement.

When an INSERT, UPDATE, or DELETE fires a trigger, the order of operation is as follows:

  1. Any BEFORE triggers are fired.

  2. Any referential actions are performed.

  3. The operation itself is performed.

  4. Any AFTER triggers are fired.

If any of the steps encounters an error that is not handled within a procedure or trigger, the preceding steps are undone, the subsequent steps are not performed, and the operation that fired the trigger fails.

Top of page  Dropping triggers

Once a trigger is created, it remains in the database until it is explicitly removed. You must have ALTER permissions on the table associated with the trigger.

The following statement removes the trigger mytrigger from the database:

DROP TRIGGER mytrigger

Top of page  Trigger execution permissions

You cannot grant permissions to execute a trigger, as triggers are not executed by users: they are fired by Adaptive Server Anywhere in response to actions on the database. Nevertheless, a trigger does have permissions associated with it as it executes, defining its right to carry out certain actions.

Triggers execute using the permissions of the owner of the table on which they are defined, not the permissions of the user that caused the trigger to fire, and not the permissions of the user that created the trigger.

When a trigger refers to a table, it uses the group memberships of the table creator to locate tables with no explicit owner name specified. For example, if a trigger on user_1.Table_A references Table_B and does not specify the owner of Table_B, then either Table_B must have been created by user_1 or user_1 must be a member of a group (directly or indirectly) that is the owner of Table_B. If neither condition is met, a table not found message results when the trigger is fired.

Also, user_1 must have permission to carry out the operations specified in the trigger.

Top of page  

Collection Contents Index Introduction to user-defined functions Introduction to batches pdf/chap10.pdf