SQL/MX 2.x Reference Manual (H06.04+)

SQL/MX Statements
HP NonStop SQL/MX Reference Manual—540440-003
2-98
Considerations for CREATE TRIGGER
Authorization and Availability Requirements
To create a trigger, you must own the schema where the trigger is defined and the
schema where the subject table of the schema resides and you must have
REFERENCES privileges on the columns used on the referenced table. Otherwise,
you must be the super ID.
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.)
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