SQL/MX 3.2.1 Reference Manual (H06.26+, J06.15+)

SQL/MX Statements
HP NonStop SQL/MX Release 3.2.1 Reference Manual—691117-005
2-147
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 create a trigger as a BEFORE or AFTER trigger. When a triggering statement
occurs, the following 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. The following table summarizes the transition variables that different trigger
types can use. “new row” refers to the transition variable and “new table” refers to the
transition table:
BEFORE Triggers
BEFORE triggers are used for one of these purposes:
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