A trigger defines a set of actions that are performed in response to an insert, update, or delete operation on a specified table. When such an SQL operation is executed, the trigger is said to have been activated.
Triggers are optional and are defined using the CREATE TRIGGER statement.
Triggers can be used, along with referential constraints and check constraints, to enforce data integrity rules. Triggers can also be used to cause updates to other tables, automatically generate or transform values for inserted or updated rows, or invoke functions to perform tasks such as issuing alerts.
Triggers are a useful mechanism for defining and enforcing transitional business rules, which are rules that involve different states of the data (for example, a salary that cannot be increased by more than 10 percent).
Using triggers places the logic that enforces business rules inside the database. This means that applications are not responsible for enforcing these rules. Centralized logic that is enforced on all of the tables means easier maintenance, because changes to application programs are not required when the logic changes.
The following are specified when creating a trigger:
The subject table specifies the table for which the trigger is defined.
The trigger event defines a specific SQL operation that modifies the subject table. The event can be an insert, update, or delete operation.
The trigger activation time specifies whether the trigger should be activated before or after the trigger event occurs.
The statement that causes a trigger to be activated includes a set of affected rows. These are the rows of the subject table that are being inserted, updated, or deleted. The trigger granularity specifies whether the actions of the trigger are performed once for the statement or once for each of the affected rows.
The triggered action consists of an optional search condition and a set of SQL statements that are executed whenever the trigger is activated. The SQL statements are only executed if the search condition evaluates to true. If the trigger activation time is before the trigger event, triggered actions can include statements that select, set transition variables, or signal SQLstates. If the trigger activation time is after the trigger event, triggered actions can include statements that select, insert, update, delete, or signal SQLstates.
The triggered action can refer to the values in the set of affected rows using transition variables. Transition variables use the names of the columns in the subject table, qualified by a specified name that identifies whether the reference is to the old value (before the update) or the new value (after the update). The new value can also be changed using the SET Variable statement in before, insert, or update triggers.
Another means of referring to the values in the set of affected rows is to use transition tables. Transition tables also use the names of the columns in the subject table, but specify a name to allow the complete set of affected rows to be treated as a table. Transition tables can only be used in after triggers, and separate transition tables can be defined for old and new values.
Multiple triggers can be specified for a combination of table, event (INSERT, UPDATE, DELETE), or activation time (BEFORE, AFTER, INSTEAD OF). When more than one trigger exists for a particular table, event, and activation time, the order in which the triggers are activated is the same as the order in which they were created. Thus, the most recently created trigger is the last trigger to be activated.
The activation of a trigger might cause trigger cascading, which is the result of the activation of one trigger that executes SQL statements that cause the activation of other triggers or even the same trigger again. The triggered actions might also cause updates resulting from the application of referential integrity rules for deletions that can, in turn, result in the activation of additional triggers. With trigger cascading, a chain of triggers and referential integrity delete rules can be activated, causing significant change to the database as a result of a single INSERT, UPDATE, or DELETE statement.
Trigger creation guidelines
When creating a trigger, you must associate it with a table. This table is called the subject table of the trigger. The term modify operation refers to any change in the state of the subject table. A modify operation is initiated by:
an INSERT statement
an UPDATE statement, or a referential constraint which performs an UPDATE
a DELETE statement, or a referential constraint which performs a DELETE
You must associate each trigger with one of these three types of modify operations. The association is called the trigger event for that particular trigger.
You must also define the action, called the triggered action, that the trigger performs when its trigger event occurs. The triggered action consists of one or more SQL statements which can execute either before or after the database manager performs the trigger event. Once a trigger event occurs, the database manager determines the set of rows in the subject table that the modify operation affects and executes the trigger.
When creating a trigger, you must declare the following attributes and behavior:
The name of the trigger.
The name of the subject table.
The trigger activation time (BEFORE or AFTER the modify operation executes).
The trigger event (INSERT, DELETE, or UPDATE).
The old values transition variable, if any.
The new values transition variable, if any.
The old values transition table, if any.
The new values transition table, if any.
The granularity (FOR EACH STATEMENT or FOR EACH ROW).
The triggered action of the trigger (including a triggered action condition and triggered SQL statement(s)).
If the trigger event is UPDATE, then the trigger column list for the trigger event of the trigger, as well as an indication of whether the trigger column list was explicit or implicit.