A trigger defines a set of actions that are executed at, or triggered by, a delete, insert, or update operation on a specified table. When such an SQL operation is executed, the trigger is said to be activated.
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 to define and enforce transitional business rules which are rules that involve different states of the data (for example, salary cannot be increased by more than 10 percent). For rules that do not involve more than one state of the data, check and referential integrity constraints should be considered.
Using triggers places the logic to enforce the business rules in the database and relieves the applications using the tables from having to enforce it. Centralized logic enforced on all the tables means easier maintenance, since no application program changes are required when the logic changes.
Triggers are optional and are defined using the CREATE TRIGGER statement.
There are a number of criteria that are defined when creating a trigger which are used to determine when a trigger should be activated.
The statement that causes a trigger to be activated will include a set of affected rows. These are the rows of the subject table that are being deleted, inserted or updated. The trigger granularity defines whether the actions of the trigger will be performed once for the statement or once for each of the rows in the set of 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. When the trigger activation time is before the trigger event, triggered action can include statements that select, set transition variables, and signal sqlstates. When the trigger activation time is after the trigger event, triggered action can include statements that select, update, insert, delete, and signal sqlstates.
The triggered action may refer to the values in the set of affected rows. This is supported through the use of 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 (prior to the update) or the new value (after the update). The new value can also be changed using the SET transition-variable statement in before update or insert triggers. Another means of referring to the values in the set of affected rows is using transition tables. Transition tables also use the names of the columns of the subject table but have a name specified that allows the complete set of affected rows to be treated as a table. As with transition variables, a transition table can be defined for the old values and the new values but only in after triggers.
Multiple triggers can be specified for a combination of table, event, or 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 will be the last trigger activated.
The activation of a trigger may cause trigger cascading. This 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 may also cause updates as a result of the original modification, or as a result of referential integrity delete rules which may result in the activation of additional triggers. With trigger cascading, a significant chain of triggers and referential integrity delete rules may be activated causing significant change to the database as a result of a single delete, insert or update statement.