相関名および疑似レコード

ノート:

このトピックの内容は、行レベルで起動されるトリガーにのみ適用されます。次のものが該当します:

  • 行レベルの単純なDMLトリガー

  • 行レベルのタイミング・セクションを持つ複合DMLトリガー

行レベルで起動されるトリガーでは、相関名を使用して処理中の行のデータにアクセスできます。デフォルトの相関名はOLDNEWおよびPARENTです。相関名を変更するには、CREATE TRIGGER文のREFERENCING句を使用します(「referencing_clause ::=」を参照)。

ネストした表にトリガーが作成されている場合、OLDおよびNEWはネストした表の現在の行を参照し、PARENTは親表の現在の行を参照します。表またはビューに対してトリガーが作成される場合、OLDおよびNEWはその表またはビューの現在の行を参照し、PARENTは未定義になります。

OLDNEWおよびPARENTは、レコード構造を保持しているものの、使用が許可されるコンテキストがレコードより少ないため、疑似レコードとも呼ばれます。疑似レコードの構造は、table_name%ROWTYPEであり、ここでtable_nameはトリガーが作成される表の名前(OLDおよびNEWの場合)または親表の名前(PARENTの場合)です。

単純なトリガーのtrigger_bodyまたは複合トリガーのtps_bodyでは、相関名はバインド変数のプレースホルダです。疑似レコードのフィールドを参照するには、次の構文を使用します。

:pseudorecord_name.field_name

条件付きトリガーのWHEN句では、相関名はバインド変数のプレースホルダではありません。そのため、前述の構文にあるコロンは省略してください。

表10-4に、トリガーを起動する文の処理対象となる行のOLDフィールドとNEWフィールドの値を示します。

表10-4 OLDおよびNEW疑似レコードのフィールド値

トリガーを起動する文 OLDフィールド値 NEWフィールド値

INSERT

NULL

挿入後の値

UPDATE

更新前の値

更新後の値

DELETE

削除前の値

NULL

疑似レコードの制限は次のとおりです。

  • 疑似レコードは、レコードレベルの操作では使用できません。

    たとえば、トリガーに次の文を含めることはできません。

    :NEW := NULL;
    
  • 疑似レコードは、サブプログラムの実パラメータとして使用できません。

    (疑似レコードのフィールドは、サブプログラムの実パラメータとして使用できます。)

  • トリガーでは、OLDフィールドの値を変更できません。

    この操作を試行すると、ORA-04085が呼び出されます。

  • トリガーを起動する文がDELETEの場合、トリガーでは、NEWフィールドの値を変更できません。

    この操作を試行すると、ORA-04084が呼び出されます。

  • トリガーを起動する文は、トリガーの起動前に実行されるため、AFTERトリガーでは、NEWフィールドの値を変更できません。

    この操作を試行すると、ORA-04084が呼び出されます。

BEFOREトリガーでは、トリガーを起動するINSERT文またはUPDATE文が表に値を設定する前に、NEWフィールドの値を変更できます。

文がBEFOREトリガーとAFTERトリガーの両方を起動する場合、BEFOREトリガーがNEWフィールドの値を変更すると、AFTERトリガーではその変更を認識できます。

例10-14 トリガーによるEMPLOYEES.SALARYの変更の記録

この例では、ログ表を作成し、UPDATE文がEMPLOYEES表のSALARY列に影響を与えた後にそのログ表に行を挿入するトリガーを作成します。その後、EMPLOYEES.SALARYを更新し、ログ表を表示します。

ログ表の作成:

DROP TABLE Emp_log;
CREATE TABLE Emp_log (
  Emp_id     NUMBER,
  Log_date   DATE,
  New_salary NUMBER,
  Action     VARCHAR2(20));
 

EMPLOYEES.SALARYの更新後にログ表に行を挿入するトリガーの作成:

CREATE OR REPLACE TRIGGER log_salary_increase
  AFTER UPDATE OF salary ON employees
  FOR EACH ROW
BEGIN
  INSERT INTO Emp_log (Emp_id, Log_date, New_salary, Action)
  VALUES (:NEW.employee_id, SYSDATE, :NEW.salary, 'New Salary');
END;
/

EMPLOYEES.SALARYの更新:

UPDATE employees
SET salary = salary + 1000.0
WHERE Department_id = 20;
 

結果:

2 rows updated.
 

ログ表の表示:

SELECT * FROM Emp_log;
 

結果:

    EMP_ID LOG_DATE  NEW_SALARY ACTION
---------- --------- ---------- --------------------
       201 28-APR-10      13650 New Salary
       202 28-APR-10       6300 New Salary
 
2 rows selected.

例10-15 条件付きトリガーによる給与変更情報の出力

この例では、DELETE文、INSERT文またはUPDATE文がEMPLOYEES表に影響するたびに給与変更情報を(それが社長の情報でなければ)出力する条件付きトリガーを作成します。データベースでは、影響を受ける行ごとにWHEN条件が評価されます。影響を受ける行に対してWHEN条件がTRUEになると、トリガーを起動する文が実行される前にその行でトリガーが起動されます。影響を受ける行に対してWHEN条件がTRUEにならない場合、トリガーはその行で起動されませんが、トリガーを起動する文はそのまま実行されます。

CREATE OR REPLACE TRIGGER print_salary_changes
  BEFORE DELETE OR INSERT OR UPDATE ON employees
  FOR EACH ROW
  WHEN (NEW.job_id <> 'AD_PRES')  -- do not print information about President
DECLARE
  sal_diff  NUMBER;
BEGIN
  sal_diff  := :NEW.salary  - :OLD.salary;
  DBMS_OUTPUT.PUT(:NEW.last_name || ': ');
  DBMS_OUTPUT.PUT('Old salary = ' || :OLD.salary || ', ');
  DBMS_OUTPUT.PUT('New salary = ' || :NEW.salary || ', ');
  DBMS_OUTPUT.PUT_LINE('Difference: ' || sal_diff);
END;
/

問合せ:

SELECT last_name, department_id, salary, job_id
FROM employees
WHERE department_id IN (10, 20, 90)
ORDER BY department_id, last_name;
 

結果:

LAST_NAME                 DEPARTMENT_ID     SALARY JOB_ID
------------------------- ------------- ---------- ----------
Whalen                               10       4200 AD_ASST
Davis                                20       6000 MK_REP
Martinez                             20      13000 MK_MAN
Garcia                               90      17000 AD_VP
King                                 90      24000 AD_PRES
Yang                                 90      17000 AD_VP
 
6 rows selected.

トリガーを起動する文:

UPDATE employees
SET salary = salary * 1.05
WHERE department_id IN (10, 20, 90);

結果:

Whalen: Old salary = 4200, New salary = 4410, Difference: 210
Martinez: Old salary = 13000, New salary = 13650, Difference: 650
Davis: Old salary = 6000, New salary = 6300, Difference: 300
Yang: Old salary = 17000, New salary = 17850, Difference: 850
Garcia: Old salary = 17000, New salary = 17850, Difference: 850
 
6 rows updated.

問合せ:

SELECT salary FROM employees WHERE job_id = 'AD_PRES';

結果:

    SALARY
----------
     25200
 
1 row selected.

例10-16 トリガーによるCLOB列の変更

この例では、CLOB列を変更するUPDATEトリガーを作成します。

TO_CLOBおよび他の変換ファンクションの詳細は、『Oracle Database SQL言語リファレンス』を参照してください。

DROP TABLE tab1;
CREATE TABLE tab1 (c1 CLOB);
INSERT INTO tab1 VALUES ('<h1>HTML Document Fragment</h1><p>Some text.', 3);

CREATE OR REPLACE TRIGGER trg1
  BEFORE UPDATE ON tab1
  FOR EACH ROW
BEGIN
  DBMS_OUTPUT.PUT_LINE('Old value of CLOB column: '||:OLD.c1);
  DBMS_OUTPUT.PUT_LINE('Proposed new value of CLOB column: '||:NEW.c1);

  :NEW.c1 := :NEW.c1 || TO_CLOB('<hr><p>Standard footer paragraph.');

  DBMS_OUTPUT.PUT_LINE('Final value of CLOB column: '||:NEW.c1);
END;
/ 

SET SERVEROUTPUT ON;
UPDATE tab1 SET c1 = '<h1>Different Document Fragment</h1><p>Different text.';

SELECT * FROM tab1;

例10-17 REFERENCING句のあるトリガー

この例では、相関名と同じ名前の表newを作成し、その表に対するトリガーを作成します。表名と相関名の競合を回避するため、トリガーは、相関名をNewestとして参照します。

CREATE TABLE new (
  field1  NUMBER,
  field2  VARCHAR2(20)
);

CREATE OR REPLACE TRIGGER Print_salary_changes
BEFORE UPDATE ON new
REFERENCING new AS Newest
FOR EACH ROW
BEGIN
  :Newest.Field2 := TO_CHAR (:newest.field1);
END;
/

OBJECT_VALUE疑似列

オブジェクト表に対するDMLトリガーでは、オブジェクト表の列のシステム生成名を戻す、SQL擬似列OBJECT_VALUEを参照できます。トリガーでは、OBJECT_VALUEのデータ型を持つIN仮パラメータを含むPL/SQLサブプログラムを起動することもできます。

関連項目:

例10-18では、オブジェクト表tbltblの更新を記録するための表tbl_history、およびトリガーTbl_Trgを作成します。DML文の影響を受けるtb1の行ごとにトリガーが実行され、tbl内のオブジェクトtの古い値および新しい値がtbl_historyに書き込まれます。古い値と新しい値は、:OLD.OBJECT_VALUEおよび:NEW.OBJECT_VALUEです。

nのすべての値が1増えました。mの値は0のままです。

例10-18 トリガーによるOBJECT_VALUE疑似列の参照

オブジェクト表の作成、データ入れ、および表示:

CREATE OR REPLACE TYPE t AUTHID DEFINER AS OBJECT (n NUMBER, m NUMBER)
/
CREATE TABLE tbl OF t
/
BEGIN
  FOR j IN 1..5 LOOP
    INSERT INTO tbl VALUES (t(j, 0));
  END LOOP;
END;
/
SELECT * FROM tbl ORDER BY n;

結果:

         N          M
---------- ----------
         1          0
         2          0
         3          0
         4          0
         5          0

5 rows selected.

履歴表とトリガーの作成:

CREATE TABLE tbl_history ( d DATE, old_obj t, new_obj t)
/
CREATE OR REPLACE TRIGGER Tbl_Trg
  AFTER UPDATE ON tbl
  FOR EACH ROW
BEGIN
  INSERT INTO tbl_history (d, old_obj, new_obj)
  VALUES (SYSDATE, :OLD.OBJECT_VALUE, :NEW.OBJECT_VALUE);
END Tbl_Trg;
/
 

オブジェクト表の更新:

UPDATE tbl SET tbl.n = tbl.n+1
/
 

結果:

5 rows updated.

古い値と新しい値の表示:

BEGIN
  FOR j IN (SELECT d, old_obj, new_obj FROM tbl_history) LOOP
    DBMS_OUTPUT.PUT_LINE (
      j.d ||
      ' -- old: ' || j.old_obj.n || ' ' || j.old_obj.m ||
      ' -- new: ' || j.new_obj.n || ' ' || j.new_obj.m
    );
  END LOOP;
END;
/

結果:

28-APR-10 -- old: 1 0 -- new: 2 0
28-APR-10 -- old: 2 0 -- new: 3 0
28-APR-10 -- old: 3 0 -- new: 4 0
28-APR-10 -- old: 4 0 -- new: 5 0
28-APR-10 -- old: 5 0 -- new: 6 0