SQL/MX 2.x Reference Manual (H06.10+, J06.03+)

SQL/MX Statements
HP NonStop SQL/MX Reference Manual—544517-008
2-104
Considerations for CREATE TRIGGER
Trigger Types
You can configure triggers as BEFORE or AFTER types. When a triggering statement
occurs, this is the order of execution:
1. BEFORE triggered statements
2. Triggering statement
3. Referential actions
4. AFTER triggered statements
Execution of a statement is considered to be complete only when all cascaded triggers
are complete. When multiple triggers are activated by the same event (that is, a conflict
set), the next trigger from the original conflict set is considered only after the execution
of cascaded triggers of a specific trigger is complete (depth-first execution). Within a
conflict set, the order of execution is by timestamp of creation of the corresponding
trigger. Older triggers are executed first.
Statement triggers and row triggers can participate in the same conflict set and can
cascade each other. Therefore, they can appear intertwined.
Triggers use transition tables or transition variables to access old and new states of the
table or row. Statement triggers use transition tables. Row triggers use transition
variables. This table summarizes the transition variables that different trigger types can
use:
BEFORE Triggers
BEFORE triggers are used for one of these purposes:
•
To generate an appropriate signal when an insert, update, or delete operation is
applied and a certain condition is satisfied (using the SIGNAL statement as an
action.)
•
To massage data prior to the insert or update operation that caused the trigger to
be activated (using the SET statement as an action.)
BEFORE-type trigger operations are exercised as tentative executions. The triggering
statement is executed but assigns values to the NEW ROW transition variables rather
than to the subject table. That table appears not to be affected by the tentative
execution. When it is accessed by the trigger action, it shows values in place before
Triggering Event and
Activation Time
Row Trigger Can Use: Statement Trigger Can Use:
BEFORE INSERT NEW ROW Invalid
BEFORE UPDATE OLD ROW, NEW ROW Invalid
BEFORE DELETE OLD ROW Invalid
AFTER INSERT NEW ROW NEW TABLE
AFTER UPDATE OLD ROW, NEW ROW OLD TABLE, NEW TABLE
AFTER DELETE OLD ROW OLD TABLE