The REFERENCING and FOR Clauses

The REFERENCING clause can declare correlation names for the original value and for the updated value in columns of the table_object that the FOR clause specifies.

REFERENCING and FOR Clauses

Notes:

¹ Use path no more than once


Element	Description	Restrictions	Syntax
correlation	Name that you declare here to qualify an old or new column value (as correlation.column) in a trigger routine	Must not be table_object	Identifier
owner	Owner of table_object	Must own table_object	Owner name
table_object	Name or synonym of a table or view whose triggers can call function	Must exist in the local database	Identifier

If you include the REFERENCING and FOR table_object clauses immediately after the parameter list of the CREATE FUNCTION statement, the function that you create is known as a trigger function (or trigger UDR or trigger routine). The FOR clause specifies the table or view whose triggers can invoke the function from the FOR EACH ROW section of their Triggered Action list.

In the REFERENCING clause, the OLD correlation specifies a prefix by which the trigger routine can reference the value that a column of table_object had before the trigger routine modifies that column value. The NEW correlation specifies a prefix for referencing the new value that the trigger routine assigns to the column. Whether the trigger routine can use correlation names to reference the OLD column value, the NEW column value, or both values depends on the type of triggering event:

A trigger routine invoked by an Insert trigger can reference only the NEW correlation name.
A trigger routine invoked by a Delete trigger or by a Select trigger can reference only the OLD correlation name.
A trigger routine invoked by an Update trigger can reference both the OLD and the NEW correlation names.

For information about how to use the correlation.column notation in triggered actions, see REFERENCING Clauses.

Besides the general requirements for any HCL OneDB™ UDR that is written in the SPL language, trigger routines can support certain additional syntax features, and are subject to certain restrictions, that are not features (or that are not restrictions) for ordinary UDRs that are not trigger routines:

A trigger routine must include the FOR table_object clause that specifies the name of the table or view in the local database whose triggers can invoke this routine.
A trigger routine can also include the REFERENCING clause to declare correlation names for OLD and NEW values that SPL statements in the UDR can reference.
Trigger routines can be invoked only in the FOR EACH ROW section of the Triggered Action list in the trigger definition.
Correlated variables for OLD or NEW values can appear in the IF statement of SPL and in CASE expressions.
Correlated variables for OLD values cannot be on the left-hand side of a LET expression.
Correlated variables for NEW values cannot be on the left-hand side of a LET expression if the FOR clause specifies a view whose INSTEAD OF trigger action list invokes the trigger routine.
Only correlated variables for NEW values can be on the left-hand side of a LET expression that references correlated variables. In this case, however, the FOR clause must specify a table, rather than a view.
Both OLD and NEW values can be on the right-hand side of a LET expression.
The Boolean operators SELECTING, INSERTING, DELETING, and UPDATING are valid in trigger routines (and only in trigger routines and in other UDRs that are invoked in triggered action statements) in contexts where Boolean expressions are valid. These operators return TRUE ('t') if the triggering event matches the DML operation referenced by the name of the operator, and they return FALSE ('f') otherwise.
If a single triggering event activates multiple triggers on the same table or view, then all of the BEFORE actions take place before any of the FOR EACH ROW actions, and all of the AFTER actions follow the FOR EACH ROW actions. The order of execution of different triggers on the same event is not guaranteed.
Trigger routines must be written in the SPL language. They cannot be written in an external language, like the C or Java™ language, but they can include calls to external language routines, such as the mi_trigger application programming interface for trigger introspection.
Trigger functions cannot reference savepoints. Any changes to the data values or to the schema of the database by a triggered action must be committed or rolled back in their entirety. HCL OneDB does not support the partial rollback of triggered actions.

For more information about the mi_trigger API, refer to the HCL OneDB DataBlade® API Programmer's Guide and to the HCL OneDB DataBlade API Function Reference.

If you include the REFERENCING clause but omit the FOR clause, or if you include the FOR clause but omit the REFERENCING clause, the CREATE FUNCTION statement fails with an error.

If you omit the REFERENCING and FOR clauses, the UDR cannot use the SELECTING, INSERTING, DELETING, and UPDATING operators, and cannot declare variables that can represent and manipulate column values in triggered actions on the table or view that the trigger definition specifies.

See the REFERENCING Clauses section in the CREATE TRIGGER statement description for the syntax of the REFERENCING clause for Delete, Insert, Select, and Update triggers.