About this task
There are three activation times that you can specify:
BEFORE, AFTER, or INSTEAD OF:
- 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. Hence, the subject table will only be modified after the
BEFORE trigger has completed execution for each row. 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 has
been completed, and after the database manager checks all constraints
that the trigger event might affect, including actions of referential
constraints. Note that AFTER triggers can have a granularity of either
FOR EACH ROW or FOR EACH STATEMENT.
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
UPDATE COMPANY_STATS SET NBEMP = NBEMP + 1
- If the activation time is INSTEAD OF, the triggered actions are
activated for each row in the set of affected rows instead of executing
the trigger event. INSTEAD OF triggers must have a granularity of
FOR EACH ROW, and the subject table must be a view. No other triggers
are able to use a view as the subject table.
Example
The following diagram illustrates the execution model of
BEFORE and AFTER triggers:
For a given table with both before and AFTER triggers, and
a modifying event that is associated with these triggers, all the
BEFORE triggers are activated first. The first activated BEFORE trigger
for a given event operates on the set of rows targeted by the operation
and makes any update modifications to the set that its logic prescribes.
The output of this BEFORE trigger is accepted as input by the next
before-trigger. When all of the BEFORE triggers that are activated
by the event have been fired, the intermediate result set, the result
of the BEFORE trigger modifications to the rows targeted by the trigger
event operation, is applied to the table. Then each AFTER trigger
associated with the event is fired. The AFTER triggers might modify
the same table, another table, or perform an action external to the
database.
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.
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 constraint validations. Consequently, you
can use them mostly to perform operations that an application can
also perform. For example:
- Perform follow on modify 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.
In contrast, you can view an INSTEAD OF trigger as a
description of the inverse operation of the view it is defined on.
For example, if the select list in the view contains an expression
over a table, the INSERT statement in the body of its INSTEAD OF INSERT
trigger will contain the reverse expression.
Because of the
different nature of BEFORE, AFTER, and INSTEAD OF triggers, a different
set of SQL operations can be used to define the triggered actions
of BEFORE and AFTER, INSTEAD OF 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.
Similarly, different trigger granularities are supported in BEFORE,
AFTER, and INSTEAD OF triggers.
The triggered SQL statement
of all triggers can be a dynamic compound statement. However, BEFORE
triggers face some restrictions; they cannot contain the following
SQL statements:
- UPDATE
- DELETE
- INSERT
- MERGE
DB2 10.5 for Linux, UNIX, and Windows
