相関名および疑似レコード
ノート:
このトピックの内容は、行レベルで起動されるトリガーにのみ適用されます。次のものが該当します:
-
行レベルの単純なDMLトリガー
-
行レベルのタイミング・セクションを持つ複合DMLトリガー
行レベルで起動されるトリガーでは、相関名を使用して処理中の行のデータにアクセスできます。デフォルトの相関名はOLD
、NEW
およびPARENT
です。相関名を変更するには、CREATE
TRIGGER
文のREFERENCING
句を使用します(「referencing_clause ::=」を参照)。
ネストした表にトリガーが作成されている場合、OLD
およびNEW
はネストした表の現在の行を参照し、PARENT
は親表の現在の行を参照します。表またはビューに対してトリガーが作成される場合、OLD
およびNEW
はその表またはビューの現在の行を参照し、PARENT
は未定義になります。
OLD
、NEW
および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フィールド値 |
---|---|---|
|
|
挿入後の値 |
|
更新前の値 |
更新後の値 |
|
削除前の値 |
|
疑似レコードの制限は次のとおりです。
-
疑似レコードは、レコードレベルの操作では使用できません。
たとえば、トリガーに次の文を含めることはできません。
: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サブプログラムを起動することもできます。
関連項目:
-
OBJECT_VALUE
の一般情報は、『Oracle Database SQL言語リファレンス』を参照してください。 -
疑似列の概要は、『Oracle Database SQL言語リファレンス』を参照してください。
例10-18では、オブジェクト表tbl
、tbl
の更新を記録するための表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