Application Development Guide

Trigger Activation Time

The trigger activation time specifies when the trigger should be activated. That is, either BEFORE or AFTER the trigger event executes. For example, the activation time of the following trigger is AFTER the INSERT operation on employee.

    CREATE TRIGGER NEW_HIRE 
      AFTER INSERT ON EMPLOYEE 
      FOR EACH ROW MODE DB2SQL 
      UPDATE COMPANY_STATS SET NBEMP = NBEMP + 1

If the activation time is BEFORE, the triggered actions are activated for each row in the set of affected rows before the trigger event executes. Note that BEFORE triggers must have a granularity of FOR EACH ROW.

If the activation time is AFTER, the triggered actions are activated for each row in the set of affected rows or for the statement, depending on the trigger granularity. This occurs after the trigger event executes, and after the database manager checks all constraints that the trigger event may affect, including actions of referential constraints. Note that AFTER triggers can have a granularity of either FOR EACH ROW or FOR EACH STATEMENT.

The different activation times of triggers reflect different purposes of triggers. Basically, BEFORE triggers are an extension to the constraint subsystem of the database management system. Therefore, you generally use them to:

• Perform validation of input data,
• Automatically generate values for newly inserted rows
• Read from other tables for cross-referencing purposes.

BEFORE triggers are not used for further modifying the database because they are activated before the trigger event is applied to the database. Consequently, they are activated before integrity constraints are checked and may be violated by the trigger event.

Conversely, you can view AFTER triggers as a module of application logic that runs in the database every time a specific event occurs. As a part of an application, AFTER triggers always see the database in a consistent state. Note that they are run after the integrity constraints that may be violated by the triggering SQL operation have been checked. Consequently, you can use them mostly to perform operations that an application can also perform. For example:

• Perform follow on update operations in the database
• Perform actions outside the database, for example, to support alerts. Note that actions performed outside the database are not rolled back if the trigger is rolled back.

Because of the different nature of BEFORE and AFTER triggers, a different set of SQL operations can be used to define the triggered actions of BEFORE and AFTER triggers. For example, update operations are not allowed in BEFORE triggers because there is no guarantee that integrity constraints will not be violated by the triggered action. The set of SQL operations you can specify in BEFORE and AFTER triggers are described in Triggered Action. Similarly, different trigger granularities are supported in BEFORE and AFTER triggers. For example, FOR EACH STATEMENT is not allowed in BEFORE triggers because there is no guarantee that constraints will not be violated by the triggered action, which would, in turn, result in failure of the operation.