Operation
As described above, if a procedure routine and a trigger are defined when an SQL statement that updates the table is
executed, the procedure routine specified in the triggered SQL statement is automatically executed to suppress any
updating that may impair data consistency. The user can define a trigger that checks table for every linked row of the
rows that reference one another in the procedure routine sot that data integrity between the tables can be ensured.
The user can also define a trigger that automatically deletes corresponding data in child tables when a row in the
parent table is deleted.
However, do not define any complex transaction logic with a trigger. A trigger operates as an extension of table
updates that start the trigger. No transaction can be controlled in any trigger. Create complex transaction logic with
stored procedures, and specify that application program are to call procedure routine directly.
Trigger operation
A trigger operates as follows.
· Triggers are executed in the following sequence. Each chained trigger is executed in units of rows, following
this same rule.
1. Executes an SQL statement that updates the table for which a trigger is defined.
2. Loops the row affected by the SQL statement.
3. Moves to the row to be updated or deleted by the SQL statement.
4. Executes the defined BEFORE trigger.
5. Updates, deletes, or inserts the row according to the SQL statement in step 1).
6. Executes the defined AFTER trigger.
· If a trigger event is defined more than once for the same table, triggers are executed in no particular sequence.
Consider the execution sequence to not have an effect on triggers specified with the same trigger operation
point.
· When data load (rdbsloader command) or database reinitializ ation (rdbfmt command) is executed using the
utility function, any trigger defined for the execution target table does not operate.
· To update the table updated by an SQL statement that starts a trigger in the triggered SQL statement, use an
update-and-add trigger. To define such an update-and-add trigger, specify the UPDATE statement in which
ROW_ID is specified in the WHERE condition of the triggered SQL statement. For this trigger, specify AFTER
as the trigger operation point and INSERT or UPDATE as the trigger event. With another type of trigger, the
table updated by an SQL statement that starts the trigger cannot be updated in processing of the triggered
SQL statement.
47