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

SQL/MX Statements
HP NonStop SQL/MX Reference Manual—540440-003
2-99
Considerations for CREATE TRIGGER
•
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
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
constraints are checked. BEFORE-type triggers are used to condition input data, while
AFTER-type triggers encode actual application logic.
Restrictions on Triggers
•
The trigger feature does not allow the use of:
•
Publish/Subscribe's embedded update and embedded delete statements as
triggering actions or events.
•
INSERTs, UPDATEs, and DELETEs found in compound statements delimited
by BEGIN … END as triggering events.
•
Compound statements delimited by BEGIN … END as part of a triggered
action.
•
CALL statements for the triggered action. However, triggering events can be in
the body of a stored procedure in Java.
•
Positioned deletes and updates as triggered statements.
•
Subqueries in search-condition for AFTER triggers (but they are allowed
in search-condition for BEFORE triggers.)
•
Do not use triggers on SQL/MX user metadata (UMD) tables, system metadata,
and NonStop MXCS metadata tables.