602SQL Documentation Index  

Triggers in SQL

A trigger is a tool that ensures automatic execution of a program written in the SQL language when inserting, deleting or modifying records in a certain table. A trigger, for example, may guarantee that:

Triggers simplify application creation, since they move some application operation to the server. Triggers allow the definition of centralized rules valid for an entire informational system. Let's have a table of employees in a company informational system. Using triggers, you can describe all necessary actions that should be executed when an employee is hired, fired, or when an employee is moved to another department. These actions are programed in a single place but serve all applications that manipulate the employee table. The server itself ensures that these rules are followed and the data consistency is guaranteed regardless of the errors in another application.

A trigger is an object with a given name that belongs to a database application. It has the following properties:

There may be more triggers executed because of a single action. All of these triggers are executed in turn. The SQL3 standard does not specify a method to set the order of trigger execution in the trigger definition. However, it is possible that this will be specified later in the standard and added to 602SQL as well.

Trigger execution may affect database contents and therefore execute other triggers. Trigger execution may be nested at will.

Privileges are not checked when executing triggers (triggers are executed with maximum privileges). In order to prevent unauthorized data access, triggers may be created only by a user assigned to the Author role in the application the trigger belongs to. You cannot create triggers in a locked application.

INSERT and UPDATE triggers are not executed when importing data into a table . DELETE triggers are not executed when a table is deleted as a whole. Trigger execution because of actions of active referential integrity depends on the SQLOPT_NO_REFINT_TRIGGERS (4096) compatibility attribute setting.

It may be useful for debugging purposes to trace trigger execution (situation TRACE_TRIGGER_EXEC). Triggers may also be debugged (like procedures).

Triggers and transactions

A trigger is always executed as a part of the transaction the statement that triggered the trigger belongs to. If an error occurs when executing the trigger, it will have the same effect as an error in the triggering statement. The trigger cannot be rolled back without rolling back the statement and vice versa.

Rollback exception condition category errors will ignore internal handler declaration if they occur inside a trigger (handlers in the trigger and procedures called by this trigger). This error can be caught only with handlers for the triggering statement.

No explicit transaction statements may be executed inside a trigger, otherwise an error sqlstate 2D000 (SQ_INVAL_TRANS_TERM) occurs.

List of topics: