17.00 - 17.05 - REFERENCING句の使用上のルール - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQLデータ定義言語 詳細トピック

Product
Advanced SQL Engine
Teradata Database
Release Number
17.00
17.05
Release Date
2020年6月
Content Type
プログラミング リファレンス
Publication ID
B035-1184-170K-JPN
Language
日本語 (日本)
以下のルールがREFERENCING句に適用されます。
  • この句に含まれる相関名の範囲は、トリガー全体です。

    このため、その名前はトリガー定義で指定されたテーブルまたはビューの名前と重複してはなりません。

  • この句の指定では、REFERENCINGの相関名を繰り返すことができません。
  • REFERENCINGキーワードの後に、次の5つのオプションを任意の順序と組み合わせで指定できます。ただし、各オプションを指定できるのは1度だけです:
    • OLDまたはOLD ROW

      行トリガーに指定できるのは、OLDまたはOLD ROWのみです。

    • NEWまたはNEW ROW

      行トリガーに指定できるのは、OLDまたはOLD ROWのみです。

    • OLD TABLEまたはOLD_TABLE
    • NEW TABLEまたはNEW_TABLE
    • OLD_NEW_TABLE

      AFTER UPDATEトリガーに指定できるのはOLD_NEW_TABLEだけです。

      5種類の遷移テーブルすべてに、UDT列を含めることができます。

  • トリガーは、任意のOLD [ROW]、NEW [ROW]、OLD TABLE、NEW TABLE、またはOLD_NEW_TABLEを参照できますが、以下の制限があります:
    トリガーのタイプ 参照
    AFTER文トリガー 遷移テーブルのみ
    AFTER行トリガー 遷移行と遷移テーブルの両方
    BEFORE行トリガー 遷移行のみ
  • 指定のOLD TABLE、OLD_TABLE、NEW TABLE,、NEW_TABLE、およびOLD_NEW_TABLE相関名を使用して、遷移テーブルのデータにアクセスできます。

    行トリガーでは、OLD [ROW]およびNEW [ROW]相関名を使用して、このトリガーで現在処理中の遷移テーブルに含まれる個々の行の値にアクセスすることもできます。文トリガーにOLD、OLD ROW、NEW、またはNEW ROWを指定することはできません。

    カスケード行トリガーは、遷移テーブル データにアクセスできません(トリガーのカスケードと再帰処理を参照)。

  • トリガーされるアクション文subquery では、OLD TABLE、OLD_TABLE、NEW TABLEおよびNEW_TABLE相関名を使用して古いテーブルおよび新しいテーブルからデータを選択できますが、OLD [ROW]およびNEW [ROW]相関名を使用してデータを選択することはできません。
  • REFERENCING句で参照するOLD TABLE、OLD_TABLE、NEW TABLE、NEW_TABLE、およびOLD_NEW_TABLEの動作は以下のとおりです。
    遷移テーブル タイプ 遷移行の全セットに含めるタイミング
    OLD TABLE UPDATEまたはDELETEが対象テーブルに実行される前。
    NEW TABLE UPDATEまたはINSERT(デフォルト値、制約チェックなどを含む)が対象テーブルに実行された後。
    OLD_NEW_TABLE UPDATEが対象テーブルに実行される前と後の両方。

    次の例では、AFTER UPDATEトリガーに対するOLD_NEW_TABLEの有用性を示しています。以下の、きわめて簡単なinventoryテーブルがあるとします。

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

    inventory内の行は、最初は以下のようになります。

    Inventory
    prod_num avail_qty
    101 100
    201 50
    301 150

    以下のトリガー文が実行されると起動する、AFTER UPDATEトリガーが定義されているとします。

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

    このリクエストに含まれるWHERE句の条件により、prod_num 201の行は更新されません。トリガーの定義に応じて、トリガーを起動したときに考えられる遷移テーブルは次の3通りになります。

    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

    これらの遷移テーブルを前提として、このトリガーによって更新された行の更新前と更新後の値を調べる必要があるとします。これを実行するには、トリガー定義の中で、以下のSELECTリクエストをFOR EACH STATEMENT WHEN句の一部として指定します。リクエストは、以下の結果テーブルを返します。

         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

    トリガーで更新していない無意味な行(テーブル内で網がけ表示)が含まれているので、これは目的に適った出力ではありません。これらの行では、prod_numの値が101から301 (または301から101)に変更された行を示していますが、UPDATEリクエストで変更している値は、そのリクエストのSET句の条件avail_qty = avail_qty - 50によりavail_qtyの値のみです。そのため、これらの行は無意味になります。

    これらの行にフィルタをかけるには、以下に示すように、SELECTリクエストにWHERE句条件を追加して、出力のうちprod_num値が変更された行にフィルタをかけるようにする必要があります。

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

    この条件を追加すると、リクエストは次に示す結果テーブルを返します。

    OldTab NewTab
    prod_num avail_qty prod_num avail_qty
    101 100 101 50
    301 150 301 100
    目的に適った結果を得るために、Teradata Databaseは以下のステップを実行する必要があります。
    1. OldTabNewTabに結合する。
    2. 結果テーブルから不要な行を排除するため、結合操作にWHERE句述部を指定して、取り出した行にフィルタをかける。

    この余分な処理は、OLD_NEW_TABLEのみを参照するAFTER UPDATEトリガーを作成することで回避できます。2つの遷移テーブルOLD_TABLEとNEW_TABLEを使用するトリガーを作成すると、これらのテーブルを結合して作成した行から目的の行を選択するために、結合操作と述部のフィルタリングが必要になりますが、OLD_NEW_TABLEではその両方が不要になります。

    次に示すWHEN句のSELECTリクエストは、目的の行を返すための結合やWHERE句の述部を必要としません。前述のリクエストでは、述部OldTab.prod_num = NewTab.prod_numで行にフィルタをかけていましたが、このリクエストではフィルタをかける必要はありません。これは、そもそも行を人為的に作成するための結合操作がないためです。

         SELECT *
         FROM OldNewTab;

    このリクエストは、OldTabNewTabを結合したリクエストにフィルタをかけたものと同じ結果テーブルを返します。

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

    遷移テーブルを使用するためのルールを以下の表にまとめます。

    REFERENCING句の指定 使用上の情報
    OLD [ROW] AS old_transition_variable_name old_transition_variable_nameを使用すると、トリガー イベントによる変更前の遷移行の列を参照できます。
    この指定は、以下のトリガー タイプにのみ適用されます。
    • DELETE
    • MERGE UPDATE
    • UPDATE
    NEW [ROW] AS new_transition_variable_name new_transition_variable_nameを使用すると、トリガー イベントで変更された後に遷移行の列を参照できます。
    この指定は、以下のトリガー タイプにのみ適用されます。
    • INSERT
    • MERGE INSERT
    • MERGE UPDATE
    • UPDATE
    OLD_TABLE AS old_table_name

    または

    OLD TABLE AS old_table_name

    old_table_nameを使用すると、トリガー イベントによる変更前のテーブルを参照できます。
    この指定は、以下のトリガー タイプにのみ適用されます。
    • DELETE
    • MERGE UPDATE
    • UPDATE

    例えば、以下のトリガーでは、subj_tabからの削除後に、old_tabの行数をカウントし、そのカウントを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

    または

    NEW TABLE AS new_table_name

    new_table_nameを使用すると、トリガー イベントによる変更後のテーブルを参照できます。
    この指定は、以下のトリガー タイプにのみ適用されます。
    • INSERT
    • MERGE INSERT
    • MERGE UPDATE
    • UPDATE

    例えば、次のトリガーでは、subj_tabを更新した後に、new_tabの行数をカウントし、そのカウントを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 old_new_table_nameを使用すると、NEW_TABLEとOLD_TABLE遷移テーブルを結合したり、その結合の結果にフィルタをかけたりする必要なしに、トリガー イベントによる変更前と変更後の両方のテーブルを参照できます。
    この指定は、以下のトリガー タイプにのみ適用されます。
    • AFTER UPDATE

    例えば、次のトリガーでは、subj_tabに何らかの更新操作の後、条件oldc2>newc2を満たすold_new_tab内の行から4つの列すべてを選択し、それらを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およびNEW TABLEは永続的ではないので、更新できません。ただし、クエリー内で参照するときには、old、new、およびold_new遷移テーブルは実テーブルと同様に扱われます。したがって、次に示すようなリクエストによりOLD TABLEとNEW TABLE遷移テーブルが結合されます。
        SELECT *
        FROM old_table_alias, new_table_alias;

    このクエリーには結合操作が必須なので、AFTER UPDATE文トリガーには常にOLD_NEW_TABLE遷移テーブルを指定して、パフォーマンスを向上させる必要があります。