17.10 - Rules For Using the REFERENCING Clause - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Data Definition Language Detailed Topics

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
Programming Reference
Publication ID
B035-1184-171K
Language
English (United States)
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

      Note that you can only specify OLD_NEW_TABLE for AFTER UPDATE triggers.

      Any of the five transition table types can contain UDT columns.

  • Triggers can reference any OLD [ROW], NEW [ROW], OLD TABLE, NEW TABLE, or OLD_NEW_TABLE subject to the following limitations:
    THIS type of trigger … References …
    AFTER statement trigger only transition tables.
    AFTER row trigger both transition rows and transition tables.
    BEFORE row trigger only 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 currently 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).

  • 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 they 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:
    THIS transition table type … Contains the entire set of transition rows …
    OLD TABLE before any UPDATEs or DELETEs are made on the subject table.
    NEW TABLE after any UPDATEs or INSERTs are made on the subject table, including default values, constraint checks, and so on.
    OLD_NEW_TABLE both before and after any UPDATEs are made on the subject table.

    The following example demonstrates the usefulness of OLD_NEW_TABLE for AFTER UPDATE triggers. Suppose you have the following very simple inventory table:

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

    The rows in inventory initially look like this:

    Inventory
    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 is executed:

    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

    This is not the desired output because it contains nonsense rows, shaded in the table, that were not updated by the trigger. The rows make no sense because they 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. The rows that were filtered by the predicate OldTab.prod_num = NewTab.prod_num in the previous request do not need to be filtered in this request because there is no join operation to create them artificially in the first place.

    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 it was modified 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 it has been modified 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 it was modified 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 it was modified 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 it has been modified 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 to the following trigger type only.
    • AFTER UPDATE

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

    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 cannot be updated because they are not persistent. However, when referenced in a query, the old, new, and old_new transition tables are treated as if they were base tables. As a result, 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, you should always specify an OLD_NEW_TABLE transition table for your AFTER UPDATE statement triggers to achieve better performance.