SQL/MX 3.2 Reference Manual (H06.25+, J06.14+)

SQL/MX Statements
HP NonStop SQL/MX Release 3.2 Reference Manual—691117-001
2-136
Considerations for CREATE 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:

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 format input data before inserting or updating the subject table 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
the action of the trigger. Because BEFORE-triggers can only be row triggers, they use
transition variables to access old and new states of the row.
Before-type triggers do not modify tables. However, by using a SET statement, they
can assign new values only to the NEW ROW transition variables. As a result, a
BEFORE-type trigger can override the effect of the original triggering statement.
The unique features of BEFORE-type triggers are:

The triggering statement executes only after the trigger is executed.

Only row granularity is allowed.

Only the NEW ROW transition variable can be modified.

BEFORE-type triggers cannot be cascading.
One of the key differences between BEFORE- and AFTER-type triggers is their
relationship to constraints. A BEFORE-type trigger can prevent the violation of a
constraint, whereas an AFTER-type trigger cannot, because it is executed after the
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