Action Clause

Action Clause:
 
|--+-+----------------------------------------+--+----------------------------------------------+-+-->
   | |                                   (1)  |  |                                         (1)  | |
   | '-BEFORE--| Triggered Action List |------'  '-FOR EACH ROW--| Triggered Action List |------' |
   |                                           (1)                                                |
   '---FOR EACH ROW--| Triggered Action List |----------------------------------------------------'
 
>--+---------------------------------------+--------------------|
   |                                  (1)  |
   '-AFTER--| Triggered Action List |------'
 

1. See page Triggered-Action List

The action clause defines trigger actions and can specify when they occur. You must define at least one trigger action, using the keywords BEFORE, FOR EACH ROW, or AFTER to indicate when the action occurs relative to execution of the triggering statement.

You can specify actions for any or all of these three options on a single trigger, but any BEFORE action list must be specified first, and any AFTER action list must be specified last. For more information on the action clause when a REFERENCING clause is also specified, see Correlated Table Action.

BEFORE Actions

The list of BEFORE trigger actions execute once before the triggering statement executes. Even if the triggering statement does not process any rows, the database server executes the BEFORE trigger actions.

FOR EACH ROW Actions

After a row of the triggering table is processed, the database server executes all of the statements of the FOR EACH ROW trigger action list; this cycle is repeated for every row that the triggering statement processes. (But if the triggering statement does not insert, delete, update, or select any rows, the database server does not execute the FOR EACH ROW trigger actions.)

In Extended Parallel Server, you cannot define FOR EACH ROW actions on tables that have globally-detached indexes.

In Dynamic Server, the FOR EACH ROW action list of a Select trigger is executed once for each instance of a row. For example, the same row can appear more than once in the result of a query joining two tables. For more information on FOR EACH ROW actions, see FOR EACH ROW Actions.

AFTER Actions

The specified set of AFTER trigger actions executes once after the action of the triggering statement is complete. If the triggering statement does not process any rows, the AFTER trigger actions still execute.

Actions of Multiple Triggers

When an UPDATE statement activates multiple triggers, the trigger actions merge. Assume that taba has columns a, b, c, and d, as this example shows:

CREATE TABLE taba (a int, b int, c int, d int)

Next, assume that you define trig1 on columns a and c, and trig2 on columns b and d. If both triggers specify BEFORE, FOR EACH ROW, and AFTER actions, then the trigger actions are executed in the following order:

1. BEFORE action list for trigger (a, c)
2. BEFORE action list for trigger (b, d)
3. FOR EACH ROW action list for trigger (a, c)
4. FOR EACH ROW action list for trigger (b, d)
5. AFTER action list for trigger (a, c)
6. AFTER action list for trigger (b, d)

The database server treats all the triggers that are activated by the same triggering statement as a single trigger, and the trigger action is the merged-action list. All the rules that govern a trigger action apply to the merged list as one list, and no distinction is made between the two original triggers.