The REFERENCING clause is optional when creating a trigger on an INSERT/UPDATE/DELETE operation. It provides a way to reference the current column identifiers in the case of INSERT and DELETE operations, and both the old column identifier and the new updated column identifier by aliasing the column(s) on which an UPDATE operation occurs.
You must specify the OLD or NEW column_identifier to access it. A solidDB® server does not provide access to the column_identifier unless you define it using the REFERENCING subclause.
{OLD | NEW} column_name AS col_identifier
The {OLD | NEW} column_name AS col_identifier subclause of the REFERENCING clause allows you to reference the values of columns both before and after an UPDATE operation. It produces a set of old and new column values which can be passed to a stored procedure; once passed, the procedure contains logic (for example, domain constraint checking) used to determine these parameter values.
Use the OLD AS clause to alias the table’s old identifier as it exists before the UPDATE. Use the NEW AS clause to alias the table’s new identifier as it exists after the UPDATE.
If you reference both the old and new values of the same column, you must use different column_identifiers.
Each column that is referenced as NEW or OLD should have a separate REFERENCING subclause.
The statement atomicity in a trigger is such that operations made in a trigger are visible to the subsequent SQL statements inside the trigger. For example, if you execute an INSERT statement in a trigger and then also perform a select in the same trigger, then the inserted row is visible.
In the case of AFTER trigger, an inserted row or an updated row is visible in the AFTER insert trigger, but a deleted row cannot be seen for a select performed within the trigger. In the case of a BEFORE trigger, an inserted or updated row is invisible within the trigger and a deleted row is visible. In the case of an UPDATE, the pre-update values are available in a BEFORE trigger.
The table below summarizes the statement atomicity in a trigger, indicating whether the row is visible to the SELECT statement in the trigger body.