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

SAP Sybase SQL Anywhere 16.0 » SQL Anywhere Server - SQL Usage » Stored procedures, triggers, batches, and user-defined functions » Triggers


Creating a trigger on a table (SQL)

Create a trigger on a table by using the CREATE TRIGGER statement. 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 must have the CREATE ANY TRIGGER or CREATE ANY OBJECT system privilege. Additionally, you must be the owner of the table the trigger is built on or have one of the following privileges:

  • ALTER privilege on the table
  • ALTER ANY TABLE system privilege
  • ALTER ANY OBJECT system privilege

Contexte et remarques

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

  1. Connect to the database.

  2. Execute a CREATE TRIGGER statement.


The new trigger is created.


  • 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 Employees
    REFERENCING NEW AS new_employee
         DECLARE err_user_error EXCEPTION
       FOR SQLSTATE '99999';
       IF new_employee.BirthDate > 'June 6, 2001' THEN
              SIGNAL err_user_error;
       END IF;


    You may already have a trigger with the name check_birth_date in your SQL Anywhere sample database. If so, and you attempt to run the above SQL statement, an error is returned indicating that the trigger definition conflicts with existing triggers.

    This trigger fires after any row is inserted into the Employees 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, and any previous trigger effects, to be undone.

    For an INSERT statement that adds many rows to the Employees 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 second line of the example to say

    BEFORE INSERT ON Employees

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

    Sometimes it is easier to enforce constraints using declarative 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 Employees

    The REFERENCING OLD clause is independent of the timing (BEFORE or AFTER) of the trigger, and enables the delete trigger code to refer to the values in the row being deleted using the alias oldtable.

  • 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 Employees
    REFERENCING NEW AS table_after_update
                OLD AS table_before_update

    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.

 See also