9.3 DMLトリガー
DMLトリガーは、表またはビューに対して作成し、そのトリガーを起動するイベントは、DML文のDELETE、INSERTおよびUPDATEで構成されます。
MERGE文に応答して起動するトリガーを作成するには、MERGE操作の構成要素であるINSERT文およびUPDATE文に対してトリガーを作成します。
DMLトリガーは、単純なトリガーまたは複合トリガーです。
単純なDMLトリガーは、次のいずれかのタイミングで起動されます。
-
トリガーを起動する文が実行される前
(このトリガーは、
BEFORE文トリガーまたは文レベルのBEFOREトリガーと呼ばれます。) -
トリガーを起動する文が実行された後
(このトリガーは、
AFTER文トリガーまたは文レベルのAFTERトリガーと呼ばれます。) -
トリガーを起動する文の影響を受ける各行の前
(このトリガーは、
BEFORE行単位トリガーまたは行レベルのBEFOREトリガーと呼ばれます。) -
トリガーを起動する文の影響を受ける各行の後
(このトリガーは、
AFTER行単位トリガーまたは行レベルのAFTERトリガーと呼ばれます。)
トリガーをFORALLを使用するINSERT文に対して作成した場合は、それらの挿入が1つの操作として処理されます。これは、文レベルのすべてのトリガーが、挿入ごとではなく1回のみ起動されるということです。トリガーをFORALLを使用するUPDATEまたはDELETE文に対して作成した場合は、DML文ごとにそのトリガーが実行されます。これにより、挿入操作のパフォーマンスが向上します。
表またはエディショニング・ビューに対して作成された複合DMLトリガーは、前述のタイミングの1つ、一部または全部で起動できます。複合DMLトリガーを使用すると、様々なタイミングで実装したアクションで共通データを共有するアプローチを簡単にプログラムできます。
行レベルで起動される単純なDMLトリガーまたは複合DMLトリガーでは、処理中の行のデータにアクセスできます。詳細は、「相関名および疑似レコード」を参照してください。
INSTEAD OF DMLトリガーは、非エディショニング・ビューに対して、または非エディショニング・ビューのネストした表の列に対して作成されるDMLトリガーです。
INSTEAD OFトリガーを除き、トリガーを起動するUPDATE文には、列リストが含まれる場合があります。列リストが含まれていると、トリガーは、指定された列が更新される場合にのみ起動されます。列リストが省略されていると、トリガーは、関連付けられている表のいずれかの列が更新された場合に起動されます。
ここでのトピック
9.3.1 トリガーを起動するDML文を検出する条件述語
DMLトリガーのトリガーを起動するイベントは、トリガーを起動する複数の文で構成できます。複数の文のうちの1つによってトリガーが起動されると、トリガーは、次の条件述語を使用してその文を判断します。
表9-1 条件述語
| 条件述語 | TRUEになる唯一の条件 |
|---|---|
|
|
|
|
|
|
|
|
指定した列に影響する |
|
|
|
条件述語は、BOOLEAN式を指定できるすべての場所で使用できます。
例9-1 トリガーによる条件述語を使用したトリガーを起動する文の検出
この例では、条件述語を使用して、トリガーを起動する可能性のある4つの文のうちのどの文でトリガーが起動されたかを判断するDMLトリガーを作成します。
CREATE OR REPLACE TRIGGER t
BEFORE
INSERT OR
UPDATE OF salary, department_id OR
DELETE
ON employees
BEGIN
CASE
WHEN INSERTING THEN
DBMS_OUTPUT.PUT_LINE('Inserting');
WHEN UPDATING('salary') THEN
DBMS_OUTPUT.PUT_LINE('Updating salary');
WHEN UPDATING('department_id') THEN
DBMS_OUTPUT.PUT_LINE('Updating department ID');
WHEN DELETING THEN
DBMS_OUTPUT.PUT_LINE('Deleting');
END CASE;
END;
/9.3.2 INSTEAD OF DMLトリガー
INSTEAD OF DMLトリガーは、非エディショニング・ビューに対して、または非エディショニング・ビューのネストした表の列に対して作成されるDMLトリガーです。データベースでは、トリガーを起動するDML文が実行されるかわりに、INSTEAD OFトリガーが起動されます。
INSTEAD OFトリガーに条件を付けることはできません。
INSTEAD OFトリガーは、本質的に更新できないビューを更新する唯一の方法です。INSTEAD OFトリガーを設計して、対象となる操作を判断し、基礎となる表に対して適切なDML操作を実行します。
INSTEAD OFトリガーは、常に行レベルのトリガーです。INSTEAD OFトリガーは、OLD値およびNEW値を読み取ることができますが、変更することはできません。
NESTED TABLE句のあるINSTEAD OFトリガーは、トリガーを起動する文の操作対象がビューにおける特定のネストした表の列の要素である場合にのみ起動されます。このトリガーは、ネストした表の変更される各要素に対して起動されます。
関連項目:
-
更新可能なビューの詳細は、『Oracle Database SQL言語リファレンス』を参照してください
-
INSTEADOFEACHROWセクションを持つ複合DMLトリガーの詳細は、「複合DMLトリガーの構造」を参照してください
例9-2 INSTEAD OFトリガー
この例では、顧客情報および注文情報を表示するoe.order_infoビューを作成します。orders表の主キー(order_id)は結合ビューの結果セットで一意でないため、このビューは本質的に更新できません。この例では、ビューに送られるINSERT文を処理するINSTEAD OFトリガーを作成します。このトリガーは、ビューの実表(customersおよびorders)に行を挿入します。
CREATE OR REPLACE VIEW order_info AS
SELECT c.customer_id, c.cust_last_name, c.cust_first_name,
o.order_id, o.order_date, o.order_status
FROM customers c, orders o
WHERE c.customer_id = o.customer_id;
CREATE OR REPLACE TRIGGER order_info_insert
INSTEAD OF INSERT ON order_info
DECLARE
duplicate_info EXCEPTION;
PRAGMA EXCEPTION_INIT (duplicate_info, -00001);
BEGIN
INSERT INTO customers
(customer_id, cust_last_name, cust_first_name)
VALUES (
:new.customer_id,
:new.cust_last_name,
:new.cust_first_name);
INSERT INTO orders (order_id, order_date, customer_id)
VALUES (
:new.order_id,
:new.order_date,
:new.customer_id);
EXCEPTION
WHEN duplicate_info THEN
RAISE_APPLICATION_ERROR (
num=> -20107,
msg=> 'Duplicate customer or order ID');
END order_info_insert;
/
挿入行が存在しないことを示す問合せ:
SELECT COUNT(*) FROM order_info WHERE customer_id = 999;
結果:
COUNT(*)
----------
0
1 row selected.
ビューへの行の挿入:
INSERT INTO order_info VALUES
(999, 'Smith', 'John', 2500, TO_DATE('13-MAR-2001', 'DD-MON-YYYY'), 0);
結果:
1 row created.
ビューに行が挿入されたことを示す問合せ:
SELECT COUNT(*) FROM order_info WHERE customer_id = 999;
結果:
COUNT(*)
----------
1
1 row selected.
customers表に行が挿入されたことを示す問合せ:
SELECT COUNT(*) FROM customers WHERE customer_id = 999;
結果:
COUNT(*)
----------
1
1 row selected.
orders表に行が挿入されたことを示す問合せ:
SELECT COUNT(*) FROM orders WHERE customer_id = 999;
結果:
COUNT(*)
----------
1
1 row selected.例9-3 ビューのネストした表の列に対するINSTEAD OFトリガー
この例では、dept_viewビューに、CASTファンクション(『Oracle Database SQL言語リファレンス』を参照)によって作成された従業員のネストした表(emplist)が含まれます。この例では、emplist列を変更するため、その列に対するINSTEAD OFトリガーを作成します。
-- Create type of nested table element:
CREATE OR REPLACE TYPE nte
AUTHID DEFINER IS
OBJECT (
emp_id NUMBER(6),
lastname VARCHAR2(25),
job VARCHAR2(10),
sal NUMBER(8,2)
);
/
-- Created type of nested table:
CREATE OR REPLACE TYPE emp_list_ IS
TABLE OF nte;
/
-- Create view:
CREATE OR REPLACE VIEW dept_view AS
SELECT d.department_id,
d.department_name,
CAST (MULTISET (SELECT e.employee_id, e.last_name, e.job_id, e.salary
FROM employees e
WHERE e.department_id = d.department_id
)
AS emp_list_
) emplist
FROM departments d;
-- Create trigger:
CREATE OR REPLACE TRIGGER dept_emplist_tr
INSTEAD OF INSERT ON NESTED TABLE emplist OF dept_view
REFERENCING NEW AS Employee
PARENT AS Department
FOR EACH ROW
BEGIN
-- Insert on nested table translates to insert on base table:
INSERT INTO employees (
employee_id,
last_name,
email,
hire_date,
job_id,
salary,
department_id
)
VALUES (
:Employee.emp_id, -- employee_id
:Employee.lastname, -- last_name
:Employee.lastname || '@example.com', -- email
SYSDATE, -- hire_date
:Employee.job, -- job_id
:Employee.sal, -- salary
:Department.department_id -- department_id
);
END;
/
ネストした表に行を挿入する前のビューの問合せ:
SELECT emplist FROM dept_view WHERE department_id=10;
結果:
EMPLIST(EMP_ID, LASTNAME, JOB, SAL) ---------------------------------------------- EMP_LIST_(NTE(200, 'Whalen', 'AD_ASST', 4200)) 1 row selected.
ネストした表に行を挿入する前の表の問合せ:
SELECT employee_id, last_name, job_id, salary FROM employees WHERE department_id = 10;
結果:
EMPLOYEE_ID LAST_NAME JOB_ID SALARY
----------- ------------------------- ---------- ----------
200 Whalen AD_ASST 4200
1 row selected.
ネストした表への行の挿入:
INSERT INTO TABLE ( SELECT d.emplist FROM dept_view d WHERE department_id = 10 ) VALUES (1001, 'Glenn', 'AC_MGR', 10000);
ネストした表に行を挿入した後のビューの問合せ:
SELECT emplist FROM dept_view WHERE department_id=10;
結果(ページに合わせて書式設定済):
EMPLIST(EMP_ID, LASTNAME, JOB, SAL)
--------------------------------------------------------------------------------
EMP_LIST_(NTE(200, 'Whalen', 'AD_ASST', 4200),
NTE(1001, 'Glenn', 'AC_MGR', 10000))
1 row selected.
ネストした表に行を挿入した後の表の問合せ:
SELECT employee_id, last_name, job_id, salary FROM employees WHERE department_id = 10;
結果:
EMPLOYEE_ID LAST_NAME JOB_ID SALARY
----------- ------------------------- ---------- ----------
200 Whalen AD_ASST 4200
1001 Glenn AC_MGR 10000
2 rows selected.9.3.3 複合DMLトリガー
表またはエディショニング・ビューに対して作成された複合DMLトリガーは、複数のタイミングで起動できます。各タイミング部には、独自の実行部とオプションの例外処理部がありますが、それらのすべての部分から共通のPL/SQL状態にアクセスできます。共通の状態は、トリガーを起動する文でエラーが発生した場合でも、トリガーを起動する文の開始時に確立され、トリガーを起動する文の完了時に破棄されます。
非エディショニング・ビューに対して作成された複合DMLトリガーは、そのタイミング部が1つのみであるため、正確には複合トリガーではありません。
複合トリガーには条件を付けることができますが、自律型にはできません。
複合トリガーの一般的な2つの使用目的は次のとおりです。
-
定期的にバルク挿入できるように、2番目の表用の行を蓄積するため
-
変更表エラー(ORA-04091)を回避するため
ここでのトピック
9.3.3.1 複合DMLトリガーの構造
複合トリガーのオプションの宣言部は、すべてのタイミング部で使用できる変数およびサブプログラムを宣言します。トリガーが起動されると、タイミング部が実行される前に宣言部が実行されます。変数およびサブプログラムは、トリガーを起動する文の継続期間中は存在します。
非エディショニング・ビューに対して作成された複合DMLトリガーは、そのタイミング部が1つのみであるため、正確には複合トリガーではありません。非エディショニング・ビューに対して最も単純な複合DMLトリガーを作成する構文は、次のとおりです。
CREATE trigger FOR dml_event_clause ON view COMPOUND TRIGGER INSTEAD OF EACH ROW IS BEGIN statement; END INSTEAD OF EACH ROW;
表またはエディショニング・ビューに対して作成された複合DMLトリガーには、表9-2に示すタイミング部が1つ以上含まれます。トリガーに複数のタイミング部が含まれる場合、タイミング部は任意の順序で指定できますが、繰り返して使用することはできません。タイミング部がないと、そのタイミングでは何も実行されません。
表9-2 複合トリガーのタイミング部
| タイミング | セクション |
|---|---|
|
トリガーを起動する文が実行される前 |
|
|
トリガーを起動する文が実行された後 |
|
|
トリガーを起動する文の影響を受ける各行の前 |
|
|
トリガーを起動する文の影響を受ける各行の後 |
|
関連項目:
複合トリガーの構文の詳細は、「CREATE TRIGGER文」を参照してください
複合DMLトリガーに初期化部はありませんが、他のタイミング部の前に実行されるBEFORE STATEMENT部によって、必要な初期化をすべて実行できます。
複合DMLトリガーにBEFORE STATEMENT部もAFTER STATEMENT部もなく、トリガーを起動する文の影響を受ける行もない場合、トリガーは起動されません。
9.3.3.2 複合DMLトリガーの制限
複合DMLトリガーには、「トリガーの制限」の制限に加え、次の制限があります。
-
OLD、NEWおよびPARENTは宣言部、BEFORESTATEMENT部またはAFTERSTATEMENT部では使用できません。 -
BEFOREEACHROW部のみがNEWの値を変更できます。 -
あるタイミング部は、別のタイミング部で呼び出される例外を処理できません。
-
あるタイミング部に
GOTO文が含まれている場合、GOTO文のターゲットは同じタイミング部に存在している必要があります。
9.3.3.3 複合DMLトリガーのパフォーマンス上のメリット
複合DMLトリガーには、トリガーを起動する文が多数の行に影響を与える場合にパフォーマンス上のメリットがあります。
たとえば、表9-2の4つのタイミング部をすべて含む複合DMLトリガーを、次の文が起動するとします。
INSERT INTO Target SELECT c1, c2, c3 FROM Source WHERE Source.c1 > 0
トリガーのBEFORE EACH ROW部およびAFTER EACH ROW部は、列c1が0(ゼロ)より大きいSourceの各行に対して実行されますが、BEFORE STATEMENT部は、INSERT文の実行前にのみ実行され、AFTER STATEMENT部は、INSERT文の実行後にのみ実行されます。
複合DMLトリガーには、バルクSQL(「バルクSQLおよびバルク・バインド」を参照)を使用する場合により大きいパフォーマンス上のメリットがあります。
9.3.3.4 バルク挿入での複合DMLトリガーの使用
複合DMLトリガーは、定期的にバルク挿入できるように、2番目の表用の行を蓄積する場合に便利です。複合トリガーからパフォーマンス上のメリットを得るには、FORALL文にBULK COLLECT INTOを指定する必要があります(指定しないと、FORALL文は単一行のDML操作を複数回実行します)。FORALL文でのBULK COLLECT句の使用の詳細は、「FORALL文とBULK COLLECT句の併用」を参照してください。
関連項目:
使用例: 新しい表employee_salariesにhr.employees.salaryに対するすべての変更をログに記録する必要があります。単一のUPDATE文によってhr.employees表の多数の行が更新されるため、employee.salariesに行をバルク挿入する方が、行を個別に挿入するより効率的です。
解決方法: 例9-4に示すように、hr.employees表の更新に対して複合トリガーを定義します。idxまたはsalariesは、(トリガーを起動する文が中断され、再開された場合でも)トリガーが起動されるたびに初期化される状態変数であるため、これらを初期化するためにBEFORE STATEMENT部は必要ありません。
ノート:
例9-4を実行するには、DBMS_LOCKパッケージに対するEXECUTE権限が必要です。
例9-4 複合トリガーによる、別の表にある表に対する変更のログへの記録
CREATE TABLE employee_salaries (
employee_id NUMBER NOT NULL,
change_date DATE NOT NULL,
salary NUMBER(8,2) NOT NULL,
CONSTRAINT pk_employee_salaries PRIMARY KEY (employee_id, change_date),
CONSTRAINT fk_employee_salaries FOREIGN KEY (employee_id)
REFERENCES employees (employee_id)
ON DELETE CASCADE)
/
CREATE OR REPLACE TRIGGER maintain_employee_salaries
FOR UPDATE OF salary ON employees
COMPOUND TRIGGER
-- Declarative Part:
-- Choose small threshhold value to show how example works:
threshhold CONSTANT SIMPLE_INTEGER := 7;
TYPE salaries_t IS TABLE OF employee_salaries%ROWTYPE INDEX BY SIMPLE_INTEGER;
salaries salaries_t;
idx SIMPLE_INTEGER := 0;
PROCEDURE flush_array IS
n CONSTANT SIMPLE_INTEGER := salaries.count();
BEGIN
FORALL j IN 1..n
INSERT INTO employee_salaries VALUES salaries(j);
salaries.delete();
idx := 0;
DBMS_OUTPUT.PUT_LINE('Flushed ' || n || ' rows');
END flush_array;
-- AFTER EACH ROW Section:
AFTER EACH ROW IS
BEGIN
idx := idx + 1;
salaries(idx).employee_id := :NEW.employee_id;
salaries(idx).change_date := SYSTIMESTAMP;
salaries(idx).salary := :NEW.salary;
IF idx >= threshhold THEN
flush_array();
END IF;
END AFTER EACH ROW;
-- AFTER STATEMENT Section:
AFTER STATEMENT IS
BEGIN
flush_array();
END AFTER STATEMENT;
END maintain_employee_salaries;
/
部門50内のすべての従業員の給与を10%引き上げ:
UPDATE employees SET salary = salary * 1.1 WHERE department_id = 50 /
結果:
Flushed 7 rows Flushed 7 rows Flushed 7 rows Flushed 7 rows Flushed 7 rows Flushed 7 rows Flushed 3 rows 45 rows updated.
2秒間待機:
BEGIN DBMS_LOCK.SLEEP(2); END; /
部門50内のすべての従業員の給与を5%引き上げ:
UPDATE employees SET salary = salary * 1.05 WHERE department_id = 50 /
結果:
Flushed 7 rows Flushed 7 rows Flushed 7 rows Flushed 7 rows Flushed 7 rows Flushed 7 rows Flushed 3 rows 45 rows updated.
従業員表への変更がemployee_salaries表に反映されていることを確認:
SELECT employee_id, count(*) c FROM employee_salaries GROUP BY employee_id /
結果:
EMPLOYEE_ID C
----------- ----------
120 2
121 2
122 2
123 2
124 2
125 2
...
199 2
45 rows selected.9.3.3.5 複合DMLトリガーを使用した変更表エラーの回避
複合DMLトリガーは、「変更表の制限」で説明されている変更表エラー(ORA-04091)を回避する場合に便利です。
使用例: ビジネス・ルールに、従業員の昇給はその従業員の部門の平均給与の10%を超えてはならないと記載されています。このルールを、トリガーによって実行する必要があります。
解決方法: 例9-5に示すように、hr.employees表の更新に対して複合トリガーを定義します。(トリガーを起動する文が中断され、再開された場合でも)トリガーが起動されるたびに、状態変数は初期化されます。
例9-5 複合トリガーによる変更表エラーの回避
CREATE OR REPLACE TRIGGER Check_Employee_Salary_Raise
FOR UPDATE OF Salary ON Employees
COMPOUND TRIGGER
Ten_Percent CONSTANT NUMBER := 0.1;
TYPE Salaries_t IS TABLE OF Employees.Salary%TYPE;
Avg_Salaries Salaries_t;
TYPE Department_IDs_t IS TABLE OF Employees.Department_ID%TYPE;
Department_IDs Department_IDs_t;
-- Declare collection type and variable:
TYPE Department_Salaries_t IS TABLE OF Employees.Salary%TYPE
INDEX BY VARCHAR2(80);
Department_Avg_Salaries Department_Salaries_t;
BEFORE STATEMENT IS
BEGIN
SELECT AVG(e.Salary), NVL(e.Department_ID, -1)
BULK COLLECT INTO Avg_Salaries, Department_IDs
FROM Employees e
GROUP BY e.Department_ID;
FOR j IN 1..Department_IDs.COUNT() LOOP
Department_Avg_Salaries(Department_IDs(j)) := Avg_Salaries(j);
END LOOP;
END BEFORE STATEMENT;
AFTER EACH ROW IS
BEGIN
IF :NEW.Salary - :Old.Salary >
Ten_Percent*Department_Avg_Salaries(:NEW.Department_ID)
THEN
Raise_Application_Error(-20000, 'Raise too big');
END IF;
END AFTER EACH ROW;
END Check_Employee_Salary_Raise;9.3.4 参照整合性を保証するトリガー
表9-3に示すとおり、トリガーおよび制約を使用して親表と子表の間の参照整合性を保持できます。(制約の詳細は、『Oracle Database SQL言語リファレンス』を参照してください。)
表9-3 参照整合性を保証する制約およびトリガー
| 表 | 表で宣言する制約 | 表で作成するトリガー |
|---|---|---|
|
親 |
|
親表への挿入にアクションは不要です(依存外部キーは存在しないため)。 |
|
子 |
この外部キー制約を無効化して、対応する |
|
ここでのトピック
ノート:
後続のトピックの例では、列Deptnoを共有する次の表を使用します。
CREATE TABLE emp (
Empno NUMBER NOT NULL,
Ename VARCHAR2(10),
Job VARCHAR2(9),
Mgr NUMBER(4),
Hiredate DATE,
Sal NUMBER(7,2),
Comm NUMBER(7,2),
Deptno NUMBER(2) NOT NULL);
CREATE TABLE dept (
Deptno NUMBER(2) NOT NULL,
Dname VARCHAR2(14),
Loc VARCHAR2(13),
Mgr_no NUMBER,
Dept_type NUMBER);
一部のトリガーには、行をロックする文(SELECT FOR UPDATE)が含まれています。この操作は、行の処理中に同時実行性を保持するために必要です。
これらの例は、記述されているとおりに使用する必要はありません。これらの例は、ユーザー独自のトリガーの設計を支援するために提供されています。
9.3.4.1 子表に対する外部キー・トリガー
例9-6のトリガーは、INSERT文またはUPDATE文が外部キー値に影響する前に、対応する値が親キー内に確実に存在することを保証します。例外ORA-04091(変更表エラー)によって、emp_dept_checkトリガーをUPDATE_SET_DEFAULTトリガーおよびUPDATE_CASCADEトリガーとともに使用できるようになります。emp_dept_checkトリガーを単独で使用する場合、この例外は必要ありません。
例9-6 子表に対する外部キー・トリガー
CREATE OR REPLACE TRIGGER emp_dept_check
BEFORE INSERT OR UPDATE OF Deptno ON emp
FOR EACH ROW WHEN (NEW.Deptno IS NOT NULL)
-- Before row is inserted or DEPTNO is updated in emp table,
-- fire this trigger to verify that new foreign key value (DEPTNO)
-- is present in dept table.
DECLARE
Dummy INTEGER; -- Use for cursor fetch
Invalid_department EXCEPTION;
Valid_department EXCEPTION;
Mutating_table EXCEPTION;
PRAGMA EXCEPTION_INIT (Invalid_department, -4093);
PRAGMA EXCEPTION_INIT (Valid_department, -4092);
PRAGMA EXCEPTION_INIT (Mutating_table, -4091);
-- Cursor used to verify parent key value exists.
-- If present, lock parent key's row so it cannot be deleted
-- by another transaction until this transaction is
-- committed or rolled back.
CURSOR Dummy_cursor (Dn NUMBER) IS
SELECT Deptno FROM dept
WHERE Deptno = Dn
FOR UPDATE OF Deptno;
BEGIN
OPEN Dummy_cursor (:NEW.Deptno);
FETCH Dummy_cursor INTO Dummy;
-- Verify parent key.
-- If not found, raise user-specified error code and message.
-- If found, close cursor before allowing triggering statement to complete:
IF Dummy_cursor%NOTFOUND THEN
RAISE Invalid_department;
ELSE
RAISE Valid_department;
END IF;
CLOSE Dummy_cursor;
EXCEPTION
WHEN Invalid_department THEN
CLOSE Dummy_cursor;
Raise_application_error(-20000, 'Invalid Department'
|| ' Number' || TO_CHAR(:NEW.deptno));
WHEN Valid_department THEN
CLOSE Dummy_cursor;
WHEN Mutating_table THEN
NULL;
END;
/9.3.4.2 親表に対するUPDATEおよびDELETE RESTRICTトリガー
例9-7のトリガーは、dept表の主キーに対してUPDATEおよびDELETE RESTRICT参照アクションを実行します。
注意:
例9-7のトリガーは、自己参照型の表(主キーまたは一意キーおよび外部キーが存在する表)では機能しません。また、このトリガーでは、トリガーの循環(AがBを起動し、BがAを起動する場合など)は実行できません。
例9-7 親表に対するUPDATEおよびDELETE RESTRICTトリガー
CREATE OR REPLACE TRIGGER dept_restrict
BEFORE DELETE OR UPDATE OF Deptno ON dept
FOR EACH ROW
-- Before row is deleted from dept or primary key (DEPTNO) of dept is updated,
-- check for dependent foreign key values in emp;
-- if any are found, roll back.
DECLARE
Dummy INTEGER; -- Use for cursor fetch
employees_present EXCEPTION;
employees_not_present EXCEPTION;
PRAGMA EXCEPTION_INIT (employees_present, -4094);
PRAGMA EXCEPTION_INIT (employees_not_present, -4095);
-- Cursor used to check for dependent foreign key values.
CURSOR Dummy_cursor (Dn NUMBER) IS
SELECT Deptno FROM emp WHERE Deptno = Dn;
BEGIN
OPEN Dummy_cursor (:OLD.Deptno);
FETCH Dummy_cursor INTO Dummy;
-- If dependent foreign key is found, raise user-specified
-- error code and message. If not found, close cursor
-- before allowing triggering statement to complete.
IF Dummy_cursor%FOUND THEN
RAISE employees_present; -- Dependent rows exist
ELSE
RAISE employees_not_present; -- No dependent rows exist
END IF;
CLOSE Dummy_cursor;
EXCEPTION
WHEN employees_present THEN
CLOSE Dummy_cursor;
Raise_application_error(-20001, 'Employees Present in'
|| ' Department ' || TO_CHAR(:OLD.DEPTNO));
WHEN employees_not_present THEN
CLOSE Dummy_cursor;
END;9.3.4.3 親表に対するUPDATEおよびDELETE SET NULLトリガー
例9-8のトリガーは、dept表の主キーに対してUPDATEおよびDELETE SET NULL参照アクションを実行します。
例9-8 親表に対するUPDATEおよびDELETE SET NULLトリガー
CREATE OR REPLACE TRIGGER dept_set_null
AFTER DELETE OR UPDATE OF Deptno ON dept
FOR EACH ROW
-- Before row is deleted from dept or primary key (DEPTNO) of dept is updated,
-- set all corresponding dependent foreign key values in emp to NULL:
BEGIN
IF UPDATING AND :OLD.Deptno != :NEW.Deptno OR DELETING THEN
UPDATE emp SET emp.Deptno = NULL
WHERE emp.Deptno = :OLD.Deptno;
END IF;
END;
/9.3.4.4 親表に対するDELETE CASCADEトリガー
例9-9のトリガーは、dept表の主キーに対してDELETE CASCADE参照アクションを実行します。
ノート:
通常、DELETE CASCADEのコードは、更新および削除の両方の可能性を考慮して、UPDATE SET NULLまたはUPDATE SET DEFAULTのコードと組み合されます。
例9-9 親表に対するDELETE CASCADEトリガー
CREATE OR REPLACE TRIGGER dept_del_cascade AFTER DELETE ON dept FOR EACH ROW -- Before row is deleted from dept, -- delete all rows from emp table whose DEPTNO is same as -- DEPTNO being deleted from dept table: BEGIN DELETE FROM emp WHERE emp.Deptno = :OLD.Deptno; END; /
9.3.4.5 親表に対するUPDATE CASCADEトリガー
例9-10のトリガーは、dept表の部門番号が更新された場合に、その変更がemp表の依存外部キーに確実に伝播されることを保証します。
ノート:
dept_cascade2トリガーによってemp表が更新されるため、例9-6のemp_dept_checkトリガー(有効になっている場合)も起動されます。結果として生成される変更表エラーは、emp_dept_checkトリガーによってトラップされます。エラーのトラップが必要なトリガーは、使用環境で常に正常に動作することを保証できるように、慎重にテストしてください。
例9-10 親表に対するUPDATE CASCADEトリガー
-- Generate sequence number to be used as flag
-- for determining if update occurred on column:
CREATE SEQUENCE Update_sequence
INCREMENT BY 1 MAXVALUE 5000 CYCLE;
CREATE OR REPLACE PACKAGE Integritypackage AUTHID DEFINER AS
Updateseq NUMBER;
END Integritypackage;
/
CREATE OR REPLACE PACKAGE BODY Integritypackage AS
END Integritypackage;
/
-- Create flag col:
ALTER TABLE emp ADD Update_id NUMBER;
CREATE OR REPLACE TRIGGER dept_cascade1
BEFORE UPDATE OF Deptno ON dept
DECLARE
-- Before updating dept table (this is a statement trigger),
-- generate sequence number
-- & assign it to public variable UPDATESEQ of
-- user-defined package named INTEGRITYPACKAGE:
BEGIN
Integritypackage.Updateseq := Update_sequence.NEXTVAL;
END;
/
CREATE OR REPLACE TRIGGER dept_cascade2
AFTER DELETE OR UPDATE OF Deptno ON dept
FOR EACH ROW
-- For each department number in dept that is updated,
-- cascade update to dependent foreign keys in emp table.
-- Cascade update only if child row was not updated by this trigger:
BEGIN
IF UPDATING THEN
UPDATE emp
SET Deptno = :NEW.Deptno,
Update_id = Integritypackage.Updateseq --from 1st
WHERE emp.Deptno = :OLD.Deptno
AND Update_id IS NULL;
/* Only NULL if not updated by 3rd trigger
fired by same triggering statement */
END IF;
IF DELETING THEN
-- After row is deleted from dept,
-- delete all rows from emp table whose DEPTNO is same as
-- DEPTNO being deleted from dept table:
DELETE FROM emp
WHERE emp.Deptno = :OLD.Deptno;
END IF;
END;
/
CREATE OR REPLACE TRIGGER dept_cascade3
AFTER UPDATE OF Deptno ON dept
BEGIN UPDATE emp
SET Update_id = NULL
WHERE Update_id = Integritypackage.Updateseq;
END;
/9.3.4.6 複雑な制約チェックに対するトリガー
トリガーは、参照整合性以外の整合性規則も実行できます。例9-11のトリガーは、トリガーを起動する文の実行を許可する前に、複雑なチェックを実行します。
ノート:
例9-11では、次のデータ構造が必要です。
CREATE TABLE Salgrade (
Grade NUMBER,
Losal NUMBER,
Hisal NUMBER,
Job_classification VARCHAR2(9));例9-11 トリガーによる複雑な制約のチェック
CREATE OR REPLACE TRIGGER salary_check
BEFORE INSERT OR UPDATE OF Sal, Job ON Emp
FOR EACH ROW
DECLARE
Minsal NUMBER;
Maxsal NUMBER;
Salary_out_of_range EXCEPTION;
PRAGMA EXCEPTION_INIT (Salary_out_of_range, -4096);
BEGIN
/* Retrieve minimum & maximum salary for employee's new job classification
from SALGRADE table into MINSAL and MAXSAL: */
SELECT Losal, Hisal INTO Minsal, Maxsal
FROM Salgrade
WHERE Job_classification = :NEW.Job;
/* If employee's new salary is less than or greater than
job classification's limits, raise exception.
Exception message is returned and pending INSERT or UPDATE statement
that fired the trigger is rolled back: */
IF (:NEW.Sal < Minsal OR :NEW.Sal > Maxsal) THEN
RAISE Salary_out_of_range;
END IF;
EXCEPTION
WHEN Salary_out_of_range THEN
Raise_application_error (
-20300,
'Salary '|| TO_CHAR(:NEW.Sal) ||' out of range for '
|| 'job classification ' ||:NEW.Job
||' for employee ' || :NEW.Ename
);
WHEN NO_DATA_FOUND THEN
Raise_application_error(-20322, 'Invalid Job Classification');
END;
/9.3.4.7 複雑なセキュリティ認可に対するトリガー
トリガーは、表データに対する複雑なセキュリティ認可の実行によく使用されます。トリガーは、データベースで提供されるデータベース・セキュリティ機能では定義できない複雑なセキュリティ認可の実行にのみ使用します。たとえば、トリガーを使用して、週末および休業時間にemployee表を更新できないようにすることができます。
複雑なセキュリティ認可の実行にトリガーを使用する場合は、BEFORE文トリガーを使用することをお薦めします。BEFORE文トリガーを使用すると、次のメリットがあります。
-
トリガーを起動する文の実行が許可される前にセキュリティ・チェックが実行されるため、不正な文による無駄な作業は行われません。
-
セキュリティ・チェックは、トリガーを起動する文に影響される各行に対してではなく、トリガーを起動する文に対してのみ実施されます。
例9-12のトリガーは、ユーザーが週末または休業時間にemployee表の更新を試行した場合に例外を呼び出すことで、セキュリティを施行します。
関連項目:
データベース・セキュリティ機能の詳細は『Oracle Databaseセキュリティ・ガイド』を参照してください。
例9-12 トリガーによるセキュリティ認可の施行
CREATE OR REPLACE TRIGGER Employee_permit_changes
BEFORE INSERT OR DELETE OR UPDATE ON employees
DECLARE
Dummy INTEGER;
Not_on_weekends EXCEPTION;
Nonworking_hours EXCEPTION;
PRAGMA EXCEPTION_INIT (Not_on_weekends, -4097);
PRAGMA EXCEPTION_INIT (Nonworking_hours, -4099);
BEGIN
-- Check for weekends:
IF (TO_CHAR(Sysdate, 'DAY') = 'SAT' OR
TO_CHAR(Sysdate, 'DAY') = 'SUN') THEN
RAISE Not_on_weekends;
END IF;
-- Check for work hours (8am to 6pm):
IF (TO_CHAR(Sysdate, 'HH24') < 8 OR
TO_CHAR(Sysdate, 'HH24') > 18) THEN
RAISE Nonworking_hours;
END IF;
EXCEPTION
WHEN Not_on_weekends THEN
Raise_application_error(-20324,'Might not change '
||'employee table during the weekend');
WHEN Nonworking_hours THEN
Raise_application_error(-20326,'Might not change '
||'emp table during Nonworking hours');
END;
/9.3.4.8 透過的なイベント・ロギングに対するトリガー
特定のイベントに続いて、関連する変更をデータベースで透過的に実行する場合、トリガーは非常に有効です。
REORDERトリガーの例は、一定の条件が満たされると、必要に応じて部品を再注文するトリガーを示しています。(つまり、トリガーを起動する文が入力され、PARTS_ON_HAND値がREORDER_POINT値より小さい場合です。)
9.3.4.9 導出列値に対するトリガー
トリガーは、INSERT文またはUPDATE文で指定される値に基づいて、列の値を自動的に導出できます。このタイプのトリガーは、同じ行内の他の列値に依存する特定の列に値を強制的に設定する場合に有効です。次に示す理由から、このタイプの操作を実行するにはBEFORE行トリガーが必要です。
-
トリガーを起動する文で導出値を使用できるようにするために、
INSERTまたはUPDATEが発生する前に依存値を導出する必要があります。 -
トリガーを起動する
INSERT文またはUPDATE文によって影響される各行に対して、トリガーを起動する必要があります。
例9-13のトリガーは、行が挿入または更新されるたびに、表の新しい列値を導出します。
ノート:
例9-13では、データ構造に対する次のような変更が必要です。
ALTER TABLE Emp ADD(
Uppername VARCHAR2(20),
Soundexname VARCHAR2(20));例9-13 トリガーによる新しい列値の導出
CREATE OR REPLACE TRIGGER Derived BEFORE INSERT OR UPDATE OF Ename ON Emp /* Before updating the ENAME field, derive the values for the UPPERNAME and SOUNDEXNAME fields. Restrict users from updating these fields directly: */ FOR EACH ROW BEGIN :NEW.Uppername := UPPER(:NEW.Ename); :NEW.Soundexname := SOUNDEX(:NEW.Ename); END; /
9.3.4.10 複合更新可能ビューを作成するトリガー
ビューは、表データに対して論理ウィンドウを提供するための優れたメカニズムです。ただし、ビューの問合せが複雑になると、ビューに対するDMLから基礎となる表に対するDMLへの変換を、システムで暗黙的に実行できなくなります。この問題の解決には、INSTEAD OFトリガーが有効です。このトリガーは、ビューに対して定義でき、実際のDMLのかわりに起動されます。
書籍が書名の順に配置されているライブラリ・システムについて考えてみます。このライブラリは、次の一連の書籍型オブジェクトで構成されています。
CREATE OR REPLACE TYPE Book_t AS OBJECT ( Booknum NUMBER, Title VARCHAR2(20), Author VARCHAR2(20), Available CHAR(1) ); / CREATE OR REPLACE TYPE Book_list_t AS TABLE OF Book_t; /
次のように、表Book_tableが作成されデータが入れられます。
DROP TABLE Book_table; CREATE TABLE Book_table ( Booknum NUMBER, Section VARCHAR2(20), Title VARCHAR2(20), Author VARCHAR2(20), Available CHAR(1) ); INSERT INTO Book_table ( Booknum, Section, Title, Author, Available ) VALUES ( 121001, 'Classic', 'Iliad', 'Homer', 'Y' ); INSERT INTO Book_table ( Booknum, Section, Title, Author, Available ) VALUES ( 121002, 'Novel', 'Gone with the Wind', 'Mitchell M', 'N' ); SELECT * FROM Book_table ORDER BY Booknum;
結果:
BOOKNUM SECTION TITLE AUTHOR A
---------- -------------------- -------------------- -------------------- -
121001 Classic Iliad Homer Y
121002 Novel Gone with the Wind Mitchell M N
2 rows selected.
次のように、表Library_tableが作成されデータが入れられます。
DROP TABLE Library_table;
CREATE TABLE Library_table (Section VARCHAR2(20));
INSERT INTO Library_table (Section)
VALUES ('Novel');
INSERT INTO Library_table (Section)
VALUES ('Classic');
SELECT * FROM Library_table ORDER BY Section;
結果:
SECTION -------------------- Classic Novel 2 rows selected.
次のように、表Book_tableおよびLibrary_tableに対して複合ビューを定義し、セクションおよび各セクション内の一連の書籍を示すライブラリの論理ビューを作成できます。
CREATE OR REPLACE VIEW Library_view AS
SELECT i.Section, CAST (
MULTISET (
SELECT b.Booknum, b.Title, b.Author, b.Available
FROM Book_table b
WHERE b.Section = i.Section
) AS Book_list_t
) BOOKLIST
FROM Library_table i;
(CASTファンクションの詳細は、『Oracle Database SQL言語リファレンス』を参照してください。)
次のように、Library_viewに対してINSTEAD OFトリガーを定義することによって、このビューを更新可能にします。
CREATE OR REPLACE TRIGGER Library_trigger
INSTEAD OF
INSERT ON Library_view
FOR EACH ROW
DECLARE
Bookvar Book_t;
i INTEGER;
BEGIN
INSERT INTO Library_table
VALUES (:NEW.Section);
FOR i IN 1..:NEW.Booklist.COUNT LOOP
Bookvar := :NEW.Booklist(i);
INSERT INTO Book_table (
Booknum, Section, Title, Author, Available
)
VALUES (
Bookvar.booknum, :NEW.Section, Bookvar.Title,
Bookvar.Author, bookvar.Available
);
END LOOP;
END;
/
Library_viewへの新しい行の挿入:
INSERT INTO Library_view (Section, Booklist) VALUES ( 'History', book_list_t (book_t (121330, 'Alexander', 'Mirth', 'Y')) );
Library_viewに対する効果の確認:
SELECT * FROM Library_view ORDER BY Section;
結果:
SECTION -------------------- BOOKLIST(BOOKNUM, TITLE, AUTHOR, AVAILABLE) -------------------------------------------------------------------- Classic BOOK_LIST_T(BOOK_T(121001, 'Iliad', 'Homer', 'Y')) History BOOK_LIST_T(BOOK_T(121330, 'Alexander', 'Mirth', 'Y')) Novel BOOK_LIST_T(BOOK_T(121002, 'Gone with the Wind', 'Mitchell M', 'N')) 3 rows selected.
Book_tableに対する効果の確認:
SELECT * FROM Book_table ORDER BY Booknum;
結果:
BOOKNUM SECTION TITLE AUTHOR A
---------- -------------------- -------------------- -------------------- -
121001 Classic Iliad Homer Y
121002 Novel Gone with the Wind Mitchell M N
121330 History Alexander Mirth Y
3 rows selected.
Library_tableに対する効果の確認:
SELECT * FROM Library_table ORDER BY Section;
結果:
SECTION -------------------- Classic History Novel 3 rows selected.
同様に、ネストした表booklistに対してトリガーを定義して、このネストした表の要素の変更を処理することもできます。
9.3.4.11 ファイングレイン・アクセス制御に対するトリガー
LOGONトリガーを使用すると、アプリケーション・コンテキストに関連付けられたパッケージを実行できます。アプリケーション・コンテキストによって、データベースにログインしているユーザーのセッション関連の情報が取得されます。アプリケーションから、ユーザーのセッション情報に基づいて、ユーザーが持つアクセス権を制御できます。
ノート:
ユーザーによるファイアウォールの外側からのログインや勤務時間外のログインの防止などの特別なログオン要件がある場合は、LOGONトリガーではなくOracle Database Vaultを使用することを検討してください。Oracle Database Vaultを使用すると、ユーザーのアクセスを厳密に制御する独自の規則を作成できます。
関連項目:
-
LOGONトリガーを作成してデータベース・セッションのアプリケーション・コンテキスト・パッケージを実行する方法の詳細は、『Oracle Databaseセキュリティ・ガイド』を参照してください。 -
Oracle Database Vaultの詳細は、『Oracle Database Vault管理者ガイド』を参照してください。