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:

A new record's contents are checked before actual insertion and some values may be added to certain columns.
Other actions will be executed after the record is deleted.
The new and old values are compared on record modification, and some action is executed based on this comparison.

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:

It is bound to a certain table and responds to the table changes.
It responds to only one of these SQL actions: INSERT, DELETE or UPDATE. A trigger responding to the UPDATE action may also have a set of column specified, whose changes will activate the trigger.
It is executed either before (BEFORE) or after (AFTER) the specified action execution.
If the action affects more than one record, the trigger may be executed for each affected record separately, or for all records at once.
It may contain a condition that is evaluated before the actual trigger execution, and if not fulfilled, the trigger will not execute.
It specifies an action (written in the SQL language) that will be automatically executed, once the trigger execution conditions are fulfilled.

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:

Routines Stored on the Server

SQL Language in 602SQL

Sequences