Rules for Using the REFERENCING Clause - Teradata VantageCloud Lake

Lake - Working with SQL
Deployment
VantageCloud
Edition
Lake
Product
Teradata VantageCloud Lake
Release Number
Published
February 2025
ft:locale
en-US
ft:lastEdition
2025-11-21
dita:mapPath
jbe1714339405530.ditamap
dita:ditavalPath
pny1626732985837.ditaval
dita:id
jbe1714339405530

The REFERENCING clause allows the WHEN condition and triggered actions of a trigger to reference the set of rows in the transition table set. This indirect access to the transition table rows is useful for making comparisons between OLD and NEW rows in the subject table or for use in the triggered action.

The references are to transient and virtual tables, which can include values from the subject table either before (OLD ROW or OLD TABLE), after (NEW ROW or NEW TABLE), or before and after (OLD_NEW_TABLE) the data-changing statement. Only AFTER UPDATE statement triggers can produce OLD_NEW_TABLE transition tables.

These are distinct from direct references to the subject table. Triggers cannot make direct comparisons between before and after rows of a subject table.

A reference to the subject table in a triggered SQL statement is called an outer reference. Outer references occur when the triggered SQL statement of a WHEN clause or the WHEN clause itself refers to a column or row in the subject table.

The following rules apply to the REFERENCING clause:
  • The scope for the correlation names in the clause is the entire trigger.

    For this reason, the names cannot duplicate names of any tables or views specified in the trigger definition.

  • REFERENCING correlation names cannot be repeated in the clause specification.
  • After the REFERENCING keyword, you can specify the following five options in any order and combination; however, each option can be specified only once:
    • OLD or OLD ROW

      You can only specify OLD or OLD ROW for row triggers.

    • NEW or NEW ROW

      You can only specify OLD or OLD ROW for row triggers.

    • OLD TABLE or OLD_TABLE
    • NEW TABLE or NEW_TABLE
    • OLD_NEW_TABLE

      You can only specify OLD_NEW_TABLE for AFTER UPDATE triggers.

  • Triggers can reference any OLD [ROW], NEW [ROW], OLD TABLE, NEW TABLE, or OLD_NEW_TABLE subject to the following limitations:
    Trigger Type What Trigger References
    AFTER statement Only transition tables.
    AFTER row Transition rows and transition tables.
    BEFORE row Transition rows.
  • You can access data from a transition table using the specified OLD TABLE, OLD_TABLE, NEW TABLE, NEW_TABLE, and OLD_NEW_TABLE correlation names.

    Row triggers can also access values from single rows in the transition table for which the trigger is being processed using the OLD [ROW] and NEW [ROW] correlation names. You cannot specify OLD, OLD ROW, NEW, or NEW ROW for statement triggers.

    Cascaded row triggers cannot access transition table data (see "Cascading and Recursion of Triggers" in General Usage Guidelines for CREATE TRIGGER and REPLACE TRIGGER).

  • Triggered action statement subqueries can select data from the old and new tables using the OLD TABLE, OLD_TABLE, NEW TABLE, and NEW_TABLE correlation names, but cannot select data using the OLD [ROW] or NEW [ROW] correlation names.
  • The behavior of OLD TABLE, OLD_TABLE, NEW TABLE, NEW_TABLE, and OLD_NEW_TABLE references in the REFERENCING clause is as follows:
    Transition Table Type Table Contents
    OLD TABLE Transition rows before UPDATEs or DELETEs on subject table.
    NEW TABLE Transition rows after UPDATEs or INSERTs on subject table, including default values, constraint checks, and so on.
    OLD_NEW_TABLE Transition rows before and after UPDATEs on subject table.

    The following example demonstrates the usefulness of OLD_NEW_TABLE for AFTER UPDATE triggers.Consider the following inventory table:

    CREATE TABLE inventory (
  prod_num  INTEGER,
  avail_qty INTEGER)
UNIQUE PRIMARY INDEX (product_num);

    The rows in inventory initially look like this:

    prod_num avail_qty
    101 100
    201 50
    301 150

    Suppose you have defined an AFTER UPDATE trigger that is fired when the following triggering statement runs:

    UPDATE inventory
SET avail_qty = avail_qty - 50
WHERE prod_num IN (101, 301);

    Because of the WHERE clause condition in this request, rows for prod_num 201 are not updated. Depending on how the trigger is defined, the following three transition tables are possible when the trigger fires:

    OLD_TABLE NEW_TABLE OLD_NEW_TABLE
    Old_Value New_Value
    prod_num avail_qty prod_num avail_qty prod_num avail_qty prod_num avail_qty
    101 100 101 50 101 100 101 50
    301 150 301 100 301 150 301 100

    Given these transition tables, suppose you want to know the before and after update values for the rows updated by this trigger. To do this, you specify the following SELECT request as part of the FOR EACH STATEMENT WHEN clause in the trigger definition. The request returns the following result table:

    SELECT *
FROM OldTab, NewTab;
    OldTab NewTab
    prod_num avail_qty prod_num avail_qty
    101 100 101 50
    101 100 301 100
    301 150 101 50
    301 150 301 100

    The output is inaccurate because the trigger did not update the shaded rows. The shaded rows represent prod_num values that were either changed from 101 to 301 or from 301 to 101, and the UPDATE request only changed avail_qty values because of its SET clause condition avail_qty = avail_qty - 50.

    To filter those rows, you must add a WHERE clause condition to the SELECT request that filters any rows with changed prod_num values from the output, as follows:

    SELECT *
FROM OldTab, NewTab
WHERE OldTab.prod_num = NewTab.prod_num;

    After you add this condition, the request returns the following result table:

    OldTab NewTab
    prod_num avail_qty prod_num avail_qty
    101 100 101 50
    301 150 301 100
    To achieve the desired result, Vantage had to take the following steps:
    1. Join OldTab to NewTab.
    2. Filter the rows retrieved by the join operation with a WHERE clause predicate to eliminate undesired rows from the result table.

    You can avoid this extra processing by creating your AFTER UPDATE triggers to reference only OLD_NEW_TABLE, which eliminates both the join operation and the predicate filtering required to select the desired rows from the rows created by joining OLD_TABLE and NEW_TABLE as you must do if you create the trigger using those two transition tables instead of OLD_NEW_TABLE.

    The following WHEN clause SELECT request returns only the desired rows without need of either a join or a WHERE clause predicate. No join operation created the rows that had to be filtered by the predicate OldTab.prod_num = NewTab.prod_num in the previous request.

    SELECT *
FROM OldNewTab;

    This request returns the identical results table as the filtered request that joined OldTab to NewTab:

    OldTab NewTab
    prod_num avail_qty prod_num avail_qty
    101 100 101 50
    301 150 301 100

    The following table summarizes the rules for using transition tables.

    REFERENCING Clause Specification Usage Information
    OLD [ROW] AS old_transition_variable_name You can use old_transition_variable_name to reference columns in a transition row before its modification by a triggering event.
    This specification applies to the following trigger types only.
    • DELETE
    • MERGE UPDATE
    • UPDATE
    NEW [ROW] AS new_transition_variable_name You can use new_transition_variable_name to reference columns in a transition row after its modification by a triggering event.
    This specification applies to the following trigger types only.
    • INSERT
    • MERGE INSERT
    • MERGE UPDATE
    • UPDATE
    OLD_TABLE AS old_table_name

    or

    OLD TABLE AS old_table_name

    		 You can use old_table_name to reference a table before its modification by a triggering event.
    This specification applies to the following trigger types only.
    • DELETE
    • MERGE UPDATE
    • UPDATE

    For example, after a delete from subj_tab, the following trigger counts the number of rows in old_tab and then inserts the count into trig_count_tab.

    CREATE TRIGGER trig1
AFTER DELETE ON subj_tab
REFERENCING OLD_TABLE 
AS old_tab
FOR EACH STATEMENT
(INSERT trig__count_tab
 SELECT COUNT(*)
 FROM old_tab);
    NEW_TABLE AS new_table_name

    or

    NEW TABLE AS new_table_name

    		 You can use new_table_name to reference a table after its modification by a triggering event.
    This specification applies to the following trigger types only.
    • INSERT
    • MERGE INSERT
    • MERGE UPDATE
    • UPDATE

    For example, after an update to subj_tab, the following trigger counts the number of rows in new_tab and then inserts the count into trig_count_tab.

    CREATE TRIGGER trig1
AFTER UPDATE ON subj_tab
REFERENCING NEW_TABLE 
AS new_tab
FOR EACH STATEMENT
(INSERT trig__count_tab
 SELECT COUNT(*)
 FROM new_tab);
    OLD_NEW_TABLE AS old_new_table_name You can use old_new_table_name to reference a table both before and after its modification by a triggering event without having to join the NEW_TABLE and OLD_TABLE transition tables or having to filter the results of that join.

    This specification applies only to AFTER UPDATE triggers.

    For example, after an update to subj_tab, the following trigger inserts into trig_tab all four columns from any rows in old_new_tab that satisfy the condition oldc2>newc2:

    CREATE TRIGGER trig1
AFTER UPDATE ON subj_tab
REFERENCING OLD_NEW_TABLE 
  AS old_new_tab (oldc1, oldc2, newc1, newc2)
FOR EACH STATEMENT
(INSERT trig_tab
 SELECT oldc1, newc1, oldc2, newc2
 FROM old_new_tab
 WHERE oldc2 > newc2;);
  • OLD TABLE and NEW TABLE are not persistent and therefore cannot be updated. However, when referenced in a query, the old, new, and old_new transition tables are treated as base tables. Therefore, a request such as the following joins the OLD TABLE and NEW TABLE transition tables:
    SELECT *
FROM old_table_alias, new_table_alias;

    Because of the required join operation for this query, specify an OLD_NEW_TABLE transition table for your AFTER UPDATE statement triggers to achieve better performance.