Triggers

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.

Db2 supports two types of triggers, basic and advanced:

• Basic triggers support a limited set of SQL statements, and require the MODE DB2SQL clause on the CREATE TRIGGER statement. You can identify basic triggers by querying the SYSIBM.SYSTRIGGERS catalog table. Blank values in the SQLPL column identify basic triggers. For more information, see CREATE TRIGGER statement (basic trigger).
• Advanced triggers support a larger set of SQL statements, including SQL procedure language (SQL PL). A CREATE TRIGGER statement for an advanced trigger must not specify the MODE DB2SQL clause. Advanced triggers are supported at application compatibility level V12R1M500 or higher. You can identify advanced triggers by querying the SYSIBM.SYSTRIGGERS catalog table. 'Y' values in the SQLPL column identify advanced triggers. For more information about advanced triggers, see CREATE TRIGGER statement (advanced trigger).

For a detailed comparison of the types, see Basic and advanced triggers.

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 (also known as the triggering 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.

Basic and advanced triggers

Basic and advanced triggers offer different functionality. See the corresponding syntax diagrams for the CREATE TRIGGER (basic) and CREATE TRIGGER (advanced) SQL statements for more information. The following table identifies some behavioral differences between the two types of triggers.

Table 1. Behavioral differences of basic and advanced triggers

Behavior	Basic Trigger	Advanced Trigger
CREATE or ALTER TRIGGER statement invocation	CREATE or ALTER TRIGGER (basic) statement can be embedded in an application program or issued interactively. It is an executable statement that can be dynamically prepared only if DYNAMICRULES run behavior is implicitly or explicitly specified.	CREATE or ALTER TRIGGER (advanced) statement can be issued interactively. It is an executable statement that can be dynamically prepared only if DYNAMICRULES run behavior is implicitly or explicitly specified.
Authorization requirement	The privilege set must include SYSADM or the SELECT privilege on the table or view on which the trigger is defined, if the REFERENCING clause is included in the trigger definition.	The privilege set must include SYSADM or the SELECT privilege for the triggering table or view on which the trigger is defined.
Default encoding scheme	The default encoding scheme is Unicode.	The default encoding scheme is determined from the value of the DEFAULT APPLICATION ENCODING SCHEME field on the installation panel DSNTIPF.
Null attribute for transition variables	A transition variable is defined with the same null attribute as the column it is associated with.	All transition variables are nullable.
Unhandled warnings at completion of a trigger	Unhandled warnings are not returned to the statement that activated a trigger. When processing in a trigger completes with a warning, the warning is not returned to the statement that activated the trigger.	Unhandled warnings are returned to the statement that activated a trigger. When processing in a trigger completes with a warning, the warning is returned to the statement that activated the trigger.
Transition variables passed as OUT or INOUT arguments to a procedure	Changes to transition variables in a procedure are not visible on return to the invoking environment, and changes to transition variables (for an AFTER trigger) are not disallowed.	Changes to transition variables in a procedure are visible on return to the invoking environment, and changes to transition variables (for an AFTER trigger) are disallowed.
Stand-alone fullselect and VALUES statements	Supported.	Not supported. Use a SELECT INTO statement or VALUES INTO statement instead.