15.00 - Rules For Using the REFERENCING Clause - Teradata Database

Teradata Database SQL Data Definition Language Detailed Topics

Product
Teradata Database
Release Number
15.00
Content Type
Programming Reference
Publication ID
B035-1184-015K
Language
English (United States)

Rules For Using the REFERENCING Clause

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” on page 726).

  • 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, Teradata Database had to take the following steps.

    a Join OldTab to NewTab.

    b 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.