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

SQL/MX Statements
HP NonStop SQL/MX Reference Manual—544517-008
2-105
Considerations for CREATE TRIGGER
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.
•
You cannot define triggers on SQL/MP objects. SQL/MP objects cannot be
referenced in a trigger.
Recompilation and Triggers
User applications that change (INSERT, UPDATE, or DELETE) information in a table
are automatically recompiled when a trigger with a matching event is added or