A trigger defines a set of actions that are executed when a delete, insert, or update operation occurs on a specified table or view. When such an operation is executed, the trigger is said to be activated.
You can use triggers along with referential constraints and check constraints to enforce data integrity rules. Triggers are more powerful than constraints because you can use them to do the following things:
- Update other tables
- Automatically generate or transform values for inserted or updated rows
- Invoke functions that perform operations both inside and outside of DB2®
For example, assume that you need to prevent an update to a column when a new value exceeds a certain amount. Instead of preventing the update, you can use a trigger. The trigger can substitute a valid value and invoke a procedure that sends a notice to an administrator about the attempted invalid update.
You define triggers with the CREATE TRIGGER statement.
Triggers move the business rule application logic into the database, which results in faster application development and easier maintenance. The business rule is no longer repeated in several applications, and the rule is centralized to the trigger. For example, DB2 can check the validity of the changes that any application makes to a salary column, and you are not required to change application programs when the logic changes.
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 subject table defines the table or view for which the trigger is defined.
- The trigger event defines a specific SQL operation that modifies the subject table. The operation could be a delete, insert, or update.
- The trigger activation time defines whether the trigger should be activated before or after the trigger event is performed on the subject table.
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 deleted, inserted or updated. The trigger granularity defines whether the actions of the trigger are to be performed once for the statement, or once for each of the rows in the set of affected rows.
The trigger 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 no search condition is specified, or the specified search condition evaluates to true.
The triggered action can 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 an assignment in a before update or insert trigger.
Another means of referring to the values in the set of affected rows is by 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. Transition tables can only be used in after triggers, and cannot be modified. Separate transition tables can be defined for old and new values.
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 is the last trigger that is activated.
The activation of a trigger might 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 might also cause updates as a result of the original modification, which might result in the activation of additional triggers. With trigger cascading, a significant chain of triggers might be activated, causing significant change to the database as a result of a single delete, insert or update statement.
The actions that are performed in the trigger are considered to be part of the operation that caused the trigger to be executed.
- The database manager ensures that the operation and the triggers that are executed as a result of that operation either all complete or are all backed out. Operations that occurred prior to the triggering operation are not affected.
- The database manager effectively checks all constraints (except for a constraint with a RESTRICT delete rule) after the operation and the associated triggers have been executed.