ASA SQL User's Guide
  Using Procedures, Triggers, and Batches
    Introduction to triggers

Creating triggers

You create triggers using either Sybase Central or Interactive SQL. In Sybase Central, you can use a wizard to provide necessary information. In Interactive SQL, you can use a CREATE TRIGGER statement. For both tools, you must have DBA or RESOURCE authority to create a trigger and you must have ALTER permissions on the table associated with the trigger.

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

You cannot use COMMIT and ROLLBACK and some ROLLBACK TO SAVEPOINT statements within a trigger.

For more information, see the list of cross-references at the end of this section.

To create a new trigger for a given table (Sybase Central)

1. Open the Triggers folder of the desired table.

2. From the File menu, choose New > Trigger.

   The Trigger Creation wizard appears.

3. Follow the instructions in the wizard.

4. When the wizard finishes, you can complete the code of the trigger on the SQL tab in the right pane.

To create a new trigger for a given table (SQL)

1. Connect to a database.

2. Execute a CREATE TRIGGER statement.

Example 1: A row-level INSERT trigger 

The following trigger is an example of a row-level INSERT trigger. It checks that the birth date 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, 2001' THEN
          SIGNAL err_user_error;
     END IF;
END

This trigger fires 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, 2001.

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 fires once for each new row. If the trigger fails for any of the rows, all effects of the INSERT statement roll back.

You can specify that the trigger fires 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.

You may find it easier in some cases to enforce constraints using declaration referential integrity or CHECK constraints, rather than triggers. For example, implementing the above example with a column check constraint proves more efficient and concise:

CHECK (@col <= 'June 6, 2001')

Example 2: 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 fires after the row is deleted rather than before, by changing the first line of the example to:

CREATE TRIGGER check_birth_date AFTER DELETE ON employee

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

Example 3: 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. The table alias table_after_update refers to columns in the new row and the table alias table_before_update refers to columns in the old row.

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.

For more information, see CREATE TRIGGER statement, and Using compound statements.