トリガーは、指定のイベントが発生するたびにOracle Databaseによって自動的に起動されるストアド・プロシージャに似ています。
注意:
データベースは、システム定義のイベントのみ検出できます。独自のイベントは定義できません。
ここでのトピック
トリガーは、ストアド・プロシージャのように、データベースに格納して繰り返し起動できる名前付きPL/SQLユニットです。ストアド・プロシージャとは異なり、トリガーは、有効にしたり無効にすることができますが、明示的に起動することはできません。
トリガーは、有効の場合、データベースによって自動的に起動されます(つまり、トリガーは、トリガーを起動するイベントが発生すると、常に起動されます)。トリガーは、無効の場合、起動されません。
トリガーは、CREATE
TRIGGER
文を使用して作成します。トリガーを起動する文およびトリガーの動作対象となる項目に関して、トリガーを起動するイベントを指定します。トリガーは、項目(表、ビュー、スキーマまたはデータベース)に対して作成または定義するといいます。また、タイミングも指定します(タイミングによって、トリガーを起動する文の実行前または実行後のどちらにトリガーを起動するかと、トリガーを起動する文の影響を受ける行ごとにトリガーを起動するかどうかを決定します)。デフォルトでは、トリガーは有効な状態で作成されます。
トリガーが表またはビューに対して作成される場合、トリガーを起動するイベントはDML文で構成され、そのトリガーはDMLトリガーと呼ばれます。
crosseditionトリガーは、エディション・ベースの再定義でのみ使用されるDMLトリガーです。
トリガーがスキーマまたはデータベースに対して作成される場合、トリガーを起動するイベントは、DDLまたはデータベース操作文で構成され、そのトリガーはシステム・トリガーと呼ばれます。
条件付きトリガーは、トリガーを起動する文に影響を受ける行ごとにデータベースで評価されるSQL条件を指定するWHEN
句を含む、DMLトリガーまたはシステム・トリガーです。
トリガーで参照される表は、トリガーの起動時に、他のユーザーのトランザクションで使用されたSQL文によって変更中である可能性があります。トリガー内で実行されるSQL文は、スタンドアロンのSQL文が従う規則と同じ規則に従います。具体的には、次のことに注意してください。
トリガーの問合せでは、参照される表の読取り一貫性のある現在のマテリアライズド・ビューおよび同一トランザクション内で変更されたデータが確認されます。
トリガーの更新では、既存のデータ・ロックが解放されるまで待機してから、処理が続行されます。
INSTEAD
OF
トリガーは、次のいずれかです。
非エディショニング・ビューに対して、または非エディショニング・ビューのネストした表の列に対して作成されるDMLトリガー
CREATE
文で定義されたシステム・トリガー
データベースでは、トリガーを起動する文が実行されるかわりに、INSTEAD
OF
トリガーが起動されます。
注意:
トリガーは、通常、トリガーを起動する文の名前(DELETE
トリガーまたはLOGON
トリガーなど)、トリガーの定義対象である項目の名前(DATABASE
トリガーまたはSCHEMA
トリガーなど)またはタイミング(BEFORE
文トリガーまたはAFTER
行単位トリガーなど)を使用して呼ばれます。
関連項目:
crosseditionトリガーの詳細は、『Oracle Database開発ガイド』を参照してください
WHEN
句の詳細は、「CREATE TRIGGER文」を参照してください
トリガーを使用して、データベース管理システムをカスタマイズできます。
たとえば、トリガーを使用すると、次の操作を行うことができます。
仮想列の値の自動的な生成
イベントのログへの記録
表へのアクセスに関する統計情報の収集
ビューに対してDML文が発行された場合の表データの変更
親表と子表が分散データベースの異なるノード上にあるときの参照整合性の実行
サブスクライブ元のアプリケーションへのデータベース・イベント、ユーザー・イベントおよびSQL文に関する情報のパブリッシュ
通常の業務時間後における表に対するDML操作の防止
無効なトランザクションの防止
制約では定義できない複雑なビジネス・ルールまたは参照整合性規則の実行(「トリガーと制約の違い」を参照)
注意:
トリガーは、プログラムで構築されるものであり、無効になりやすいため、信頼性の高いセキュリティ・メカニズムではありません。高いセキュリティを確保するには、Oracle Database Vaultを使用してください(『Oracle Database Vault管理者ガイド』を参照)。
トリガーと制約の違い
トリガーと制約のどちらを使用してもデータ入力を制約できますが、両者は大きく異なります。
トリガーは、常に新規データのみに適用されます。たとえば、トリガーによって、DML文がデータベースの列にNULL
値を挿入することを防止できますが、列には、トリガーが定義される前またはトリガーが無効な間に挿入されたNULL
値が含まれている可能性があります。
制約は、トリガーのように新しいデータのみに適用するか、新しいデータと既存のデータの両方に適用できます。『Oracle Database SQL言語リファレンス』で説明されているように、制約の動作は制約の状態によって異なります。
制約は、同じルールを実行するトリガーに比べて作成しやすく、エラーが発生する可能性が低くなります。ただしトリガーは、制約では定義できない複雑なビジネス・ルールを実行できます。トリガーは、次の場合にのみデータ入力の制約に使用することをお薦めします。
親表と子表が分散データベースの異なるノード上にあるときに参照整合性を実行する場合
制約では定義できない複雑なビジネス・ルールまたは参照整合性規則を実行する場合
関連項目:
制約を使用してビジネス・ルールを実行したり、表への無効な情報の入力を防止する方法の詳細は、『Oracle Database開発ガイド』を参照してください。
トリガーおよび制約を使用して親表と子表の間の参照整合性を保持する方法の詳細は、「参照整合性を保証するトリガー」を参照してください
DMLトリガーは、表またはビューに対して作成し、そのトリガーを起動するイベントは、DML文のDELETE
、INSERT
およびUPDATE
で構成されます。
MERGE
文に応答して起動するトリガーを作成するには、MERGE
操作の構成要素であるINSERT
文およびUPDATE
文に対してトリガーを作成します。
DMLトリガーは、単純なトリガーまたは複合トリガーです。
単純なDMLトリガーは、次のいずれかのタイミングで起動されます。
トリガーを起動する文が実行される前
(このトリガーは、BEFORE
文トリガーまたは文レベルのBEFORE
トリガーと呼ばれます。)
トリガーを起動する文が実行された後
(このトリガーは、AFTER
文トリガーまたは文レベルのAFTER
トリガーと呼ばれます。)
トリガーを起動する文の影響を受ける各行の前
(このトリガーは、BEFORE
行単位トリガーまたは行レベルのBEFORE
トリガーと呼ばれます。)
トリガーを起動する文の影響を受ける各行の後
(このトリガーは、AFTER
行単位トリガーまたは行レベルのAFTER
トリガーと呼ばれます。)
表またはエディショニング・ビューに対して作成された複合DMLトリガーは、前述のタイミングの1つ、一部または全部で起動できます。複合DMLトリガーを使用すると、様々なタイミングで実装したアクションで共通データを共有するアプローチを簡単にプログラムできます。
行レベルで起動される単純なDMLトリガーまたは複合DMLトリガーでは、処理中の行のデータにアクセスできます。詳細は、「相関名および疑似レコード」を参照してください。
INSTEAD
OF
DMLトリガーは、非エディショニング・ビューに対して、または非エディショニング・ビューのネストした表の列に対して作成されるDMLトリガーです。
INSTEAD
OF
トリガーを除き、トリガーを起動するUPDATE
文には、列リストが含まれる場合があります。列リストが含まれていると、トリガーは、指定された列が更新される場合にのみ起動されます。列リストが省略されていると、トリガーは、関連付けられている表のいずれかの列が更新された場合に起動されます。
ここでのトピック
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; /
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言語リファレンス』を参照してください
INSTEAD
OF
EACH
ROW
セクションを持つ複合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 || '@company.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.
表またはエディショニング・ビューに対して作成された複合DMLトリガーは、複数のタイミングで起動できます。各タイミング部には、独自の実行部とオプションの例外処理部がありますが、それらのすべての部分から共通のPL/SQL状態にアクセスできます。共通の状態は、トリガーを起動する文でエラーが発生した場合でも、トリガーを起動する文の開始時に確立され、トリガーを起動する文の完了時に破棄されます。
非エディショニング・ビューに対して作成された複合DMLトリガーは、そのタイミング部が1つのみであるため、正確には複合トリガーではありません。
複合トリガーには条件を付けることができますが、自律型にはできません。
複合トリガーの一般的な2つの使用目的は次のとおりです。
定期的にバルク挿入できるように、2番目の表用の行を蓄積するため
変更表エラー(ORA-04091)を回避するため
ここでのトピック
複合トリガーのオプションの宣言部は、すべてのタイミング部で使用できる変数およびサブプログラムを宣言します。トリガーが起動されると、タイミング部が実行される前に宣言部が実行されます。変数およびサブプログラムは、トリガーを起動する文の継続期間中は存在します。
非エディショニング・ビューに対して作成された複合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
部もなく、トリガーを起動する文の影響を受ける行もない場合、トリガーは起動されません。
複合DMLトリガーには、「トリガーの制限」の制限に加え、次の制限があります。
OLD
、NEW
およびPARENT
は宣言部、BEFORE
STATEMENT
部またはAFTER
STATEMENT
部では使用できません。
BEFORE
EACH
ROW
部のみがNEW
の値を変更できます。
あるタイミング部は、別のタイミング部で呼び出される例外を処理できません。
あるタイミング部にGOTO
文が含まれている場合、GOTO
文のターゲットは同じタイミング部に存在している必要があります。
複合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およびバルク・バインド」を参照)を使用する場合により大きいパフォーマンス上のメリットがあります。
複合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.
複合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に示すとおり、トリガーおよび制約を使用して親表と子表の間の参照整合性を保持できます。(制約の詳細は、『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-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-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-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-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-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-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; /
トリガーは、表データに対する複雑なセキュリティ認可の実行によく使用されます。トリガーは、データベースで提供されるデータベース・セキュリティ機能では定義できない複雑なセキュリティ認可の実行にのみ使用します。たとえば、トリガーを使用して、週末および休業時間に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; /
特定のイベントに続いて、関連する変更をデータベースで透過的に実行する場合、トリガーは非常に有効です。
REORDER
トリガーの例は、一定の条件が満たされると、必要に応じて部品を再注文するトリガーを示しています。(つまり、トリガーを起動する文が入力され、PARTS_ON_HAND
値がREORDER_POINT
値より小さい場合です。)
トリガーは、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; /
ビューは、表データに対して論理ウィンドウを提供するための優れたメカニズムです。ただし、ビューの問合せが複雑になると、ビューに対する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
に対してトリガーを定義して、このネストした表の要素の変更を処理することもできます。
LOGON
トリガーを使用すると、アプリケーション・コンテキストに関連付けられたパッケージを実行できます。アプリケーション・コンテキストによって、データベースにログインしているユーザーのセッション関連の情報が取得されます。アプリケーションから、ユーザーのセッション情報に基づいて、ユーザーが持つアクセス権を制御できます。
注意:
ユーザーによるファイアウォールの外側からのログインや勤務時間外のログインの防止などの特別なログオン要件がある場合は、LOGON
トリガーではなくOracle Database Vaultを使用することを検討してください。Oracle Database Vaultを使用すると、ユーザーのアクセスを厳密に制御する独自の規則を作成できます。
関連項目:
LOGON
トリガーを作成してデータベース・セッションのアプリケーション・コンテキスト・パッケージを実行する方法の詳細は、『Oracle Databaseセキュリティ・ガイド』を参照してください。
Oracle Database Vaultの詳細は、『Oracle Database Vault管理者ガイド』を参照してください。
注意:
このトピックの内容は、行レベルで起動されるトリガーにのみ適用されます。つまり、次のトリガーです。
行レベルの単純な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
句では、相関名はバインド変数のプレースホルダではありません。そのため、前述の構文にあるコロンは省略してください。
表9-4に、トリガーを起動する文の処理対象となる行のOLD
フィールドとNEW
フィールドの値を示します。
表9-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
トリガーではその変更を認識できます。
例9-14では、ログ表を作成し、UPDATE
文がEMPLOYEES
表のSALARY
列に影響を与えた後にそのログ表に行を挿入するトリガーを作成します。その後、EMPLOYEES
.SALARY
を更新し、ログ表を表示します。
例9-15では、DELETE
文、INSERT
文またはUPDATE
文がEMPLOYEES
表に影響するたびに給与変更情報を(それが社長の情報でなければ)出力する条件付きトリガーを作成します。データベースでは、影響を受ける行ごとにWHEN
条件が評価されます。影響を受ける行に対してWHEN
条件がTRUE
になると、トリガーを起動する文が実行される前にその行でトリガーが起動されます。影響を受ける行に対してWHEN
条件がTRUE
にならない場合、トリガーはその行で起動されませんが、トリガーを起動する文はそのまま実行されます。
例9-16では、CLOB
列を変更するUPDATE
トリガーを作成します。(TO_CLOB
および他の変換ファンクションの詳細は、『Oracle Database SQL言語リファレンス』を参照してください。)
例9-17では、相関名と同じ名前の表new
を作成し、その表に対するトリガーを作成します。表名と相関名の競合を回避するため、トリガーは、相関名をNewest
として参照します。
例9-14 トリガーによる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.
例9-15 条件付きトリガーによる給与変更情報の出力
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 Fay 20 6000 MK_REP Hartstein 20 13000 MK_MAN De Haan 90 17000 AD_VP King 90 24000 AD_PRES Kochhar 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
Hartstein: Old salary = 13000, New salary = 13650, Difference: 650
Fay: Old salary = 6000, New salary = 6300, Difference: 300
Kochhar: Old salary = 17000, New salary = 17850, Difference: 850
De Haan: 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.
例9-16 トリガーによるCLOB列の変更
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;
例9-17 REFERENCING句のあるトリガー
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; /
オブジェクト表に対するDMLトリガーでは、オブジェクト表の列のシステム生成名を戻す、SQL擬似列OBJECT_VALUE
を参照できます。トリガーでは、OBJECT_VALUE
のデータ型を持つIN
仮パラメータを含むPL/SQLサブプログラムを起動することもできます。
関連項目:
OBJECT_VALUE
の一般情報は、『Oracle Database SQL言語リファレンス』を参照してください。
疑似列の概要は、『Oracle Database SQL言語リファレンス』を参照してください。
例9-18では、オブジェクト表tbl
、tbl
の更新を記録するための表tbl_history
、およびトリガーTbl_Trg
を作成します。DML文の影響を受けるtb1
の行ごとにトリガーが実行され、tbl
内のオブジェクトt
の古い値および新しい値がtbl_history
に書き込まれます。古い値と新しい値は、:OLD
.OBJECT_VALUE
および:NEW
.OBJECT_VALUE
です。
列n
のすべての値が1増えました。m
の値は0のままです。
例9-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
システム・トリガーは、スキーマまたはデータベースのいずれかに対して作成されます。このトリガーを起動するイベントは、DDL文(「ddl_event」のリストを参照)またはデータベース操作文(「database_event」のリストを参照)で構成されます。
システム・トリガーは、次のいずれかのタイミングで起動されます。
トリガーを起動する文が実行される前
(このトリガーは、BEFORE
文トリガーまたは文レベルのBEFORE
トリガーと呼ばれます。)
トリガーを起動する文が実行された後
(このトリガーは、AFTER
文トリガーまたは文レベルのAFTER
トリガーと呼ばれます。)
CREATE
文を起動するかわり
(このトリガーは、INSTEAD
OF
CREATE
トリガーと呼ばれます。)
ここでのトピック
SCHEMA
トリガーは、スキーマに対して作成され、そのスキーマの所有者であるユーザーが現行ユーザーで、トリガー・イベントを開始するたびに起動されます。
user1とuser2の両方がSCHEMAトリガーを所有していて、user2が所有するDRユニットをuser1が起動するとします。DRユニット内では、user2が現行ユーザーになります。したがって、user2が所有するSCHEMAトリガーのトリガー・イベントをDRユニットが開始すると、そのトリガーが起動されます。ただし、user1が所有するSCHEMAトリガーのトリガー・イベントをDRユニットが開始しても、そのトリガーは起動されません。
例9-19では、サンプル・スキーマHR
にBEFORE
文トリガーを作成します。HR
として接続しているユーザーがデータベース・オブジェクトを削除しようとすると、オブジェクトの削除前に、データベースによってこのトリガーが起動されます。
例9-19 サンプル・スキーマHRに対するBEFORE文トリガー
CREATE OR REPLACE TRIGGER drop_trigger BEFORE DROP ON hr.SCHEMA BEGIN RAISE_APPLICATION_ERROR ( num => -20000, msg => 'Cannot drop object'); END; /
DATABASE
トリガーは、データベースに対して作成され、データベース・ユーザーがトリガー・イベントを開始するたびに起動されます。
例9-20では、エラーのログを記録するトリガーの基本構文を示します。このトリガーは、文の実行の失敗(ログオンの失敗など)の後で起動されます。
注意:
AFTER
SERVERERROR
トリガーは、Oracleリレーショナル・データベース管理システム(RDBMS)が、エラー・トリガーを起動することが妥当であると判断した場合にのみ起動されます。AFTER
SERVERERROR
トリガーの詳細は、「CREATE TRIGGER文」を参照してください。
例9-21のトリガーは、ユーザーがデータベースにログオンした後にプロシージャcheck_user
を実行します。
例9-20 データベースに対するAFTER文トリガー
CREATE TRIGGER log_errors AFTER SERVERERROR ON DATABASE BEGIN IF (IS_SERVERERROR (1017)) THEN NULL; -- (substitute code that processes logon error) ELSE NULL; -- (substitute code that logs error code) END IF; END; /
例9-21 トリガーによるログオンの監視
CREATE OR REPLACE TRIGGER check_user AFTER LOGON ON DATABASE BEGIN check_user; EXCEPTION WHEN OTHERS THEN RAISE_APPLICATION_ERROR (-20000, 'Unexpected error: '|| DBMS_Utility.Format_Error_Stack); END; /
INSTEAD
OF
CREATE
トリガーは、トリガー・イベントがCREATE
文であるSCHEMA
トリガーです。データベースでは、トリガーを起動する文が実行されるかわりに、トリガー自体が起動されます。
例9-22は、現在のスキーマに対するINSTEAD
OF
CREATE
トリガーの基本構文を示しています。このトリガーは、現在のスキーマの所有者が現在のスキーマでCREATE
文を発行すると起動されます。
例9-22 スキーマに対するINSTEAD OF CREATEトリガー
CREATE OR REPLACE TRIGGER t INSTEAD OF CREATE ON SCHEMA BEGIN EXECUTE IMMEDIATE 'CREATE TABLE T (n NUMBER, m NUMBER)'; END; /
トリガーでは、PL/SQL、CおよびJavaで記述されたサブプログラムを起動できます。例9-4のトリガーは、PL/SQLサブプログラムを起動します。例9-23のトリガーは、Javaサブプログラムを起動します。
トリガーによって起動されるサブプログラムは、トリガー本体のコンテキスト内で実行されるため、このようなサブプログラムではトランザクション制御文は実行できません。
トリガーによって実行者権限(IR)サブプログラムが起動される場合は、トリガーを起動する文を実行したユーザーではなく、トリガーを作成したユーザーが現行ユーザーとみなされます。IRサブプログラムの詳細は、「実行者権限および定義者権限(AUTHIDプロパティ)」を参照してください。
トリガーがリモート・サブプログラムを起動し、トリガーの実行中にタイムスタンプまたはシグネチャの不一致が検出されると、リモート・サブプログラムは実行されず、トリガーは無効になります。
例9-23 トリガーによるJavaサブプログラムの起動
CREATE OR REPLACE PROCEDURE Before_delete (Id IN NUMBER, Ename VARCHAR2) IS LANGUAGE Java name 'thjvTriggers.beforeDelete (oracle.sql.NUMBER, oracle.sql.CHAR)'; CREATE OR REPLACE TRIGGER Pre_del_trigger BEFORE DELETE ON Tab FOR EACH ROW CALL Before_delete (:OLD.Id, :OLD.Ename) /
対応するJavaファイルはthjvTriggers
.java
です。
import java.sql.* import java.io.* import oracle.sql.* import oracle.oracore.* public class thjvTriggers { public static void beforeDelete (NUMBER old_id, CHAR old_name) Throws SQLException, CoreException { Connection conn = JDBCConnection.defaultConnection(); Statement stmt = conn.CreateStatement(); String sql = "insert into logtab values ("+ old_id.intValue() +", '"+ old_ename.toString() + ", BEFORE DELETE'); stmt.executeUpdate (sql); stmt.close(); return; } }
CREATE
TRIGGER
文は、トリガーをコンパイルし、そのコードをデータベースに格納します。コンパイル・エラーが発生してもトリガーは作成されますが、トリガーを起動する文は、次の場合を除いて失敗します。
トリガーが無効な状態で作成された場合。
トリガーを起動するイベントがAFTER
STARTUP
ON
DATABASE
である場合。
トリガーを起動するイベントがAFTER
LOGON
ON
DATABASE
またはAFTER
LOGON
ON
SCHEMA
のいずれかで、ユーザーがSYSTEM
としてログオンしている場合。
トリガーのコンパイル・エラーを表示するには、SQL*PlusまたはEnterprise ManagerでSHOW
ERRORS
コマンドを使用するか、または静的データ・ディクショナリ・ビュー*_ERRORS
を問い合せます(『Oracle Databaseリファレンス』を参照)。
トリガーのコンパイルに失敗すると、その例外ハンドラは実行できません。例は、「リモート例外処理」を参照してください。
トリガーがサブプログラムやパッケージなどの別のオブジェクトを参照している場合、そのオブジェクトが変更または削除されると、トリガーは無効になります。次回トリガーを起動するイベントが発生すると、コンパイラはトリガーの再有効化を試みます(詳細は、『Oracle Database開発ガイド』を参照してください)。
注意:
メッセージのエンキューにはDBMS_AQ
パッケージが使用されるため、トリガーとキューの間の依存性は維持できません。
トリガーを手動で再コンパイルするには、ALTER
TRIGGER
文を使用します(「ALTER TRIGGER文」を参照)。
ほとんどの場合、例外を呼び出す文がトリガーによって実行され、その例外が例外ハンドラによって処理されないと、データベースは、トリガーとそのトリガーを呼び出す文の両方の影響をロールバックします。
次の場合、データベースは、トリガーを起動する文の影響ではなく、トリガーの影響のみをロールバックします(また、エラーをトレース・ファイルおよびアラート・ログに記録します)。
トリガーを起動するイベントがAFTER
STARTUP
ON
DATABASE
またはBEFORE
SHUTDOWN
ON
DATABASE
である場合。
トリガーを起動するイベントがAFTER
LOGON
ON
DATABASE
で、ユーザーがADMINISTER
DATABASE
TRIGGER
権限を持っている場合。
トリガーを起動するイベントがAFTER
LOGON
ON
SCHEMA
で、ユーザーがスキーマを所有しているか、ALTER
ANY
TRIGGER
権限を持っている場合。
複合DMLトリガーの場合、データベースは、トリガーの影響ではなくトリガーを起動する文の影響のみをロールバックします。ただし、トリガーで宣言された変数は再初期化され、トリガーを起動する文のロールバック前に計算された値は、失われます。
注意:
複雑なセキュリティ認可または制約を実行するトリガーは、通常、ユーザー定義の例外を呼び出します(「ユーザー定義の例外」を参照)。
関連項目:
例外処理の詳細は、「PL/SQLのエラー処理」を参照してください
リモート例外処理
リモート・データベースにアクセスするトリガーは、リモート・データベースが使用できる場合にのみ、リモート例外処理を実行できます。ローカル・データベースでトリガーをコンパイルする必要がある場合にリモート・データベースを使用できないと、ローカル・データベースでリモート・データベースにアクセスする文の妥当性チェックを実行できないため、コンパイルは正常に実行されません。トリガーをコンパイルできないと、その例外ハンドラは実行できません。
例9-24のトリガーには、リモート・データベースにアクセスするINSERT
文が含まれます。このトリガーには、例外ハンドラも含まれます。ただし、ローカル・データベースでトリガーをコンパイルしようとした場合にリモート・データベースを使用できないと、コンパイルは正常に実行されず、例外ハンドラは実行できません。
例9-25では、例9-24の問題の回避策を示しています(つまり、リモートINSERT
文および例外ハンドラをストアド・サブプログラムに配置し、トリガーでストアド・サブプログラムを起動します)。サブプログラムは、リモート・データベースにアクセスするための妥当性チェック済の文が含まれている、コンパイル済の形式でローカル・データベースに格納されます。したがって、リモート・データベースを使用できないため、リモートINSERT
文が失敗すると、サブプログラムの例外ハンドラでその例外を処理できます。
例9-24 リモート・データベースが使用できない場合に例外を処理できないトリガー
CREATE OR REPLACE TRIGGER employees_tr
AFTER INSERT ON employees
FOR EACH ROW
BEGIN
-- When remote database is unavailable, compilation fails here:
INSERT INTO employees@remote (
employee_id, first_name, last_name, email, hire_date, job_id
)
VALUES (
99, 'Jane', 'Doe', 'jane.doe@example.com', SYSDATE, 'ST_MAN'
);
EXCEPTION
WHEN OTHERS THEN
INSERT INTO emp_log (Emp_id, Log_date, New_salary, Action)
VALUES (99, SYSDATE, NULL, 'Could not insert');
RAISE;
END;
/
例9-25 例9-24の回避策
CREATE OR REPLACE PROCEDURE insert_row_proc AUTHID CURRENT_USER AS
no_remote_db EXCEPTION; -- declare exception
PRAGMA EXCEPTION_INIT (no_remote_db, -20000);
-- assign error code to exception
BEGIN
INSERT INTO employees@remote (
employee_id, first_name, last_name, email, hire_date, job_id
)
VALUES (
99, 'Jane', 'Doe', 'jane.doe@example.com', SYSDATE, 'ST_MAN'
);
EXCEPTION
WHEN OTHERS THEN
INSERT INTO emp_log (Emp_id, Log_date, New_salary, Action)
VALUES (99, SYSDATE, NULL, 'Could not insert row.');
RAISE_APPLICATION_ERROR (-20000, 'Remote database is unavailable.');
END;
/
CREATE OR REPLACE TRIGGER employees_tr
AFTER INSERT ON employees
FOR EACH ROW
BEGIN
insert_row_proc;
END;
/
トリガーを使用して、特定のイベントが発生するたびに(どのユーザーまたはアプリケーションがトリガーを起動する文を発行するかにかかわらず)必要なアクションが実行されることを保証します。
たとえば、トリガーを使用して、いずれかのユーザーが表を更新するたびにそのログ・ファイルが更新されることを保証します。
データベース機能が重複するトリガーは作成しないでください。
たとえば、制約で同じ処理ができるときに、無効なデータを拒否するためのトリガーは作成しないでください(「トリガーと制約の違い」を参照)。
SQL文が行を処理する順序(変化する可能性あり)に依存するトリガーは作成しないでください。
たとえば、グローバル・パッケージ変数の現行の値が、行トリガーによって処理される行に依存する場合は、行トリガー内のその変数に値を代入しないでください。グローバル・パッケージ変数がトリガーによって更新される場合は、それらの変数をBEFORE
文トリガー内で初期化してください。
行データがディスクに書き込まれる前に行を変更するために、BEFORE
行トリガーを使用します。
行IDを取得して、それを操作で使用するために、AFTER
行トリガーを使用します。
トリガーを起動する文によってORA-02292エラーが発生すると、AFTER
行トリガーが起動されます。
注意:
AFTER
行トリガーを使用すると、BEFORE
行トリガーより少し効率が上がります。BEFORE
行トリガーでは、影響を受けるデータ・ブロックを最初にトリガーのために読み取り、次にトリガーを起動する文のために読み取ります。AFTER
行トリガーでは、影響を受けるデータ・ブロックをトリガーのためにのみ読み取ります。
BEFORE
文トリガーのトリガーを起動する文が、実行中のUPDATE文と競合する
UPDATE文または
DELETE
文である場合、データベースによって、SAVEPOINTまでの透過的
ROLLBACK
が実行され、トリガーを起動する文が再開されます。データベースでは、トリガーを起動する文が正常に完了するまでこの処理が何回も行われる可能性があります。データベースによってトリガーを起動する文が再開されるたびに、トリガーが起動されます。SAVEPOINT
までのROLLBACK
では、トリガーが参照するパッケージ変数への変更は取り消されません。この状況を検出するには、パッケージにカウンタ変数を含めてください。
再帰トリガーは作成しないでください。
たとえば、トリガーが定義されている表に対してUPDATE
文を発行するAFTER
UPDATE
トリガーは、作成しないでください。このトリガーは、メモリー不足になるまで再帰的に起動し続けます。
リモート・データベースにアクセスする文が含まれるトリガーを作成する場合、その文の例外ハンドラをストアド・サブプログラムに配置し、そのサブプログラムをトリガーから起動してください。
詳細は、「リモート例外処理」を参照してください。
DATABASE
トリガーは慎重に使用してください。このトリガーは、データベース・ユーザーがトリガー・イベントを開始するたびに起動されます。
トリガーが次の文を実行すると、文によってトリガーの所有者は戻されますが、表を更新しているユーザーは戻されません。
SELECT Username FROM USER_USERS;
コミットされたトリガーのみが起動されます。
トリガーは、それを作成するCREATE
TRIGGER
文が成功した後に、暗黙的にコミットされます。したがって、トリガーを作成する次の文では、そのトリガーを起動できません。
CREATE OR REPLACE TRIGGER my_trigger AFTER CREATE ON DATABASE BEGIN NULL; END; /
同じ表に対するトリガーを持つアプリケーションのインストールをモジュール化するには、一連の操作を実行する単一のトリガーではなく、同じ型の複数のトリガーを作成します。
各トリガーは、前に起動されたトリガーによって変更された内容を参照します。各トリガーは、OLD
およびNEW
の値を参照できます。
すべてのPL/SQLユニットに適用される制限に加えて(表C-1を参照)、トリガーには次の制限があります。
自律型トリガーのみがTCL文またはDDL文を実行できます。
自立型トリガーの詳細は、「自律型トリガー」を参照してください。
サブプログラムはトリガー本体のコンテキスト内で実行されるため、トリガーでは、トランザクション制御文を実行するサブプログラムを起動できません。
トリガーによって起動されるサブプログラムの詳細は、「トリガーによって起動されるサブプログラム」を参照してください。
トリガーでは、SERIALLY_REUSABLE
パッケージにアクセスできません。
SERIALLY_REUSABLE
パッケージの詳細は、「SERIALLY_REUSABLEパッケージ」を参照してください。
関連項目:
トリガーのサイズは、32K以下にしてください。
トリガーのロジックで60行をはるかに超えるPL/SQLソース・テキストが必要な場合は、そのソース・テキストの大部分をストアド・サブプログラムに組み込んで、トリガーからそのサブプログラムを起動してください。トリガーによって起動されるサブプログラムの詳細は、「トリガーによって起動されるサブプログラム」を参照してください。
注意:
Oracleは、既存アプリケーションとの下位互換性のためにのみ、LONG
およびLONG
RAW
データ型をサポートしています。
すべてのPL/SQLユニットに適用される制限に加えて(「LONGおよびLONG RAW変数」を参照)、トリガーには次の制限があります。
トリガーでは、LONG
またはLONG
RAW
データ型の変数を宣言できません。
トリガー内のSQL文では、列データをCHAR
またはVARCHAR2
データ型に変換できる場合にのみ、LONG
またはLONG
RAW
列を参照できます。
トリガーでは、LONGまたはLONG RAW
列で相関名の
NEW
またはPARENT
を使用できません。
注意:
このトピックの内容は、行レベルの単純なDMLトリガーにのみ適用されます。
変更表とは、DML文で(状況によってはDELETE
CASCADE
制約の影響によって)現在変更されている表です。(INSTEAD
OF
トリガーによって変更中のビューは、変更ビューとはみなされません。)
変更表の制限のため、トリガーは、トリガーを起動する文が変更中の表を問い合せたり、変更することはできません。行レベルのトリガーで変更表が検出されると、ORA-04091が発生し、トリガーおよびトリガーを起動する文の影響がロールバックされ、トリガーを起動する文を発行したユーザーまたはアプリケーションに制御が戻されます(例9-26を参照)。
注意:
Oracle Databaseでは、分散データベースの異なるノード上に存在する表の間の宣言参照制約はサポートされないため、リモート・ノードにアクセスするトリガーに関する変更表の制限は適用されません。
同様に、データベースでは、ループバック・データベース・リンクで接続されている同一データベース内の表に関する変更表の制限も適用されません。ループバック・データベース・リンクによって、リンクを含むデータベースに戻るOracle Netパスが定義され、ローカル表がリモートで表示されます。
トリガーを使用して変更表を更新する必要がある場合、次のいずれかの方法で変更表エラーを回避できます。
複合DMLトリガーを使用します(「複合DMLトリガーを使用した変更表エラーの回避」を参照)。
一時表を使用します。
たとえば、変更表を更新する1つのAFTER
行単位トリガーを使用するかわりに、一時表を更新するAFTER
行単位トリガーおよび一時表からの値を使用して変更表を更新するAFTER
文トリガーの2つのトリガーを使用します。
変更表の制限の緩和
Oracle Database 8gリリース1以上では、親表に対して削除を行うと、BEFORE
およびAFTER
トリガーが1回起動されます。したがって、親表および子表の問合せおよび変更を実行する行レベルと文レベルのトリガーを作成できます。制約が自己参照的でない場合、これによって、ほとんどの外部キー制約アクションはそれらのAFTER行トリガーを介して実装されます。更新カスケード、更新セットNULL、更新セット・デフォルト、削除セット・デフォルト、欠落した親の挿入および子のカウントの保持をすべて簡単に実装できます(「参照整合性を保証するトリガー」を参照)。
ただし、カスケードでは、複数行の外部キーを更新する場合に注意が必要です。外部キー制約によって、AFTER行トリガーが起動されるまでいずれの一致する外部キー行もロックされないことが保証されているため、トリガーは、別のトランザクションによってコミットされていない変更済の行を見逃すことはありません。
例9-27では、トリガーを起動する文によってp
は正常に更新されますが、トリガーがf
を更新するときに問題が発生します。まず、トリガーを起動する文はp
の値(1)から(2)への変更を行い、トリガーはf
に値(2)の行を2つ残したままf
の値(1)から(2)への更新を行います。次に、トリガーを起動する文はp
の値(2)から(3)への更新を行い、トリガーはf
の2つの行の値を両方とも(2)から(3)へ更新します。最後に、この文はp
の値(3)から(4)への更新を行い、トリガーはfの3つすべての行を(3)から(4)へ更新します。p
とf
のデータ項目間の関係は失われます。
この問題を回避するには、主キーを変更するp
の複数行更新を禁止して既存の主キー値を再利用するか、または外部キー値の更新を追跡して行が2回更新されないようにトリガーを変更します。
例9-26 トリガーによる変更表エラー
-- Create log table DROP TABLE log; CREATE TABLE log ( emp_id NUMBER(6), l_name VARCHAR2(25), f_name VARCHAR2(20) ); -- Create trigger that updates log and then reads employees CREATE OR REPLACE TRIGGER log_deletions AFTER DELETE ON employees FOR EACH ROW DECLARE n INTEGER; BEGIN INSERT INTO log VALUES ( :OLD.employee_id, :OLD.last_name, :OLD.first_name ); SELECT COUNT(*) INTO n FROM employees; DBMS_OUTPUT.PUT_LINE('There are now ' || n || ' employees.'); END; / -- Issue triggering statement: DELETE FROM employees WHERE employee_id = 197;
結果:
DELETE FROM employees WHERE employee_id = 197
*
ERROR at line 1:
ORA-04091: table HR.EMPLOYEES is mutating, trigger/function might not see it
ORA-06512: at "HR.LOG_DELETIONS", line 10
ORA-04088: error during execution of trigger 'HR.LOG_DELETIONS'
トリガーの影響がロールバックされていることの確認:
SELECT count(*) FROM log;
結果:
COUNT(*) ---------- 0 1 row selected.
トリガーを起動する文の影響がロールバックされていることの確認:
SELECT employee_id, last_name FROM employees WHERE employee_id = 197;
結果:
EMPLOYEE_ID LAST_NAME ----------- ------------------------- 197 Feeney 1 row selected.
例9-27 更新カスケード
DROP TABLE p; CREATE TABLE p (p1 NUMBER CONSTRAINT pk_p_p1 PRIMARY KEY); INSERT INTO p VALUES (1); INSERT INTO p VALUES (2); INSERT INTO p VALUES (3); DROP TABLE f; CREATE TABLE f (f1 NUMBER CONSTRAINT fk_f_f1 REFERENCES p); INSERT INTO f VALUES (1); INSERT INTO f VALUES (2); INSERT INTO f VALUES (3); CREATE TRIGGER pt AFTER UPDATE ON p FOR EACH ROW BEGIN UPDATE f SET f1 = :NEW.p1 WHERE f1 = :OLD.p1; END; /
問合せ:
SELECT * FROM p ORDER BY p1;
結果:
P1 ---------- 1 2 3
問合せ:
SELECT * FROM f ORDER BY f1;
結果:
F1 ---------- 1 2 3
トリガーを起動する文の発行:
UPDATE p SET p1 = p1+1;
問合せ:
SELECT * FROM p ORDER BY p1;
結果:
P1 ---------- 2 3 4
問合せ:
SELECT * FROM f ORDER BY f1;
結果:
F1 ---------- 4 4 4
タイミングの異なる2つ以上のトリガーを同じ表の同じ文に定義する場合、それらのトリガーは次の順序で起動されます。
すべてのBEFORE
STATEMENT
トリガー
すべてのBEFORE
EACH
ROW
トリガー
すべてのAFTER
EACH
ROW
トリガー
すべてのAFTER
STATEMENT
トリガー
場合によっては、異なるタイミングを持つ個々のトリガー群を、必要な順序でアクションを明示的にコード化した単一の複合トリガーに置き換えます。複合トリガーの詳細は、「複合DMLトリガー」を参照してください。
タイミングが同じ2つ以上のトリガーを作成する際に、起動順序が重要である場合は、FOLLOWS
句またはPRECEDES
句(「FOLLOWS | PRECEDES」を参照)を使用してその起動順序を制御できます。
1つの表に複数の複合トリガーを作成する場合、次のことに注意してください。
すべてのBEFORE
STATEMENT
セクションはBEFORE
STATEMENT
のタイミングで、BEFORE
EACH
ROW
セクションはBEFORE
EACH
ROW
のタイミングで、というように実行されます。
トリガーの実行順序がFOLLOWS
句で指定された場合、FOLLOWS
句によって複合トリガーのセクションの実行順序が決定されます。FOLLOWS
がすべてのトリガーではなく一部のトリガーに指定されている場合、トリガーの実行順序はFOLLOWS
句によって関係付けられたトリガーについてのみ保証されます。
すべてのAFTER
STATEMENT
セクションはAFTER
STATEMENT
のタイミングで、AFTER
EACH
ROW
セクションはAFTER
EACH
ROW
のタイミングで、というように実行されます。
トリガーの実行順序がPRECEDES
句で指定された場合、PRECEDES
句によって複合トリガーのセクションの実行順序が決定されます。PRECEDES
がすべてのトリガーではなく一部のトリガーに指定されている場合、トリガーの実行順序はPRECEDES
句によって関係付けられたトリガーについてのみ保証されます。
注意:
PRECEDES
は、reverse crosseditionトリガーにのみ適用されます。詳細は、『Oracle Database開発ガイド』を参照してください。
複合トリガーの起動は、単純なトリガーの起動でインターリーブされる可能性があります。
あるトリガーによって他のトリガーが起動される場合、それらのトリガーはカスケードしていると言われます。データベースでは、一度に最大32個のトリガーをカスケードできます。カスケードするトリガーの数を制限する場合、トリガーを起動するたびにカーソルがオープンされるため、OPEN_CURSORS
初期化パラメータ(『Oracle Databaseリファレンス』を参照)を使用します。
デフォルトでは、トリガーは、CREATE
TRIGGER
文によって有効な状態で作成されます。無効な状態でトリガーを作成するには、DISABLE
を指定します。無効な状態でトリガーを作成すると、有効化する前にトリガーがエラーなしでコンパイルされることを確認できます。
次の理由がある場合、トリガーを一時的に無効にできます。
トリガーが、使用できないオブジェクトを参照している場合
大規模なデータ・ロードを実行する必要があり、トリガーを起動せずに迅速に処理する場合
データをリロードする場合
単一のトリガーを有効化または無効化するには、次の文を使用します。
ALTER TRIGGER [schema.]trigger_name { ENABLE | DISABLE };
特定の表に対して作成されたすべてのエディションのすべてのトリガーを有効化または無効化するには、次の文を使用します。
ALTER TABLE table_name { ENABLE | DISABLE } ALL TRIGGERS;
前述の2つの文で、schema
は、トリガーを含むスキーマの名前で、デフォルトは自分のスキーマです。
関連項目:
ALTER
TRIGGER
文の詳細は、「ALTER TRIGGER文」を参照してください
ALTER
TABLE
文の詳細は、『Oracle Database SQL言語リファレンス』を参照してください。
トリガーを変更するには、そのトリガーを置換または再作成する必要があります。(ALTER
TRIGGER
文は、トリガーの有効化、無効化、コンパイルまたは名前変更のみを実行します。)
トリガーを置換するには、OR
REPLACE
句を指定してCREATE
TRIGGER
文を使用します。
トリガーを再作成するには、最初にトリガーをDROP
TRIGGER
文を使用して削除してから、CREATE
TRIGGER
文を使用して再作成します。
トリガーをデバッグする場合、ストアド・サブプログラムで使用可能な機能を使用できます。これらの機能の詳細は、『Oracle Database開発ガイド』を参照してください。
関連項目:
CREATE
TRIGGER
文の詳細は、「CREATE TRIGGER文」を参照してください
DROP
TRIGGER
文の詳細は、「DROP TRIGGER文」を参照してください
ALTER
TRIGGER
文の詳細は、「ALTER TRIGGER文」を参照してください
データベースにデータを転送する(トリガーを起動する可能性のある)Oracle Databaseユーティリティには、次のものがあります。
SQL*Loader (sqlldr
)
SQL*Loaderは、外部ファイルからOracle Databaseの表にデータをロードします。
SQL*Loaderによる従来型ロードの実行中に、INSERT
トリガーが起動されます。
SQL*Loaderによるダイレクト・ロードの前に、トリガーは無効になります。
関連項目:
SQL*Loaderの詳細は、『Oracle Databaseユーティリティ』を参照してください。
データ・ポンプ・インポート(impdp
)
データ・ポンプ・インポート(impdp
)は、データ・ポンプ・エクスポート(expdp
)によって作成されたエクスポート・ダンプ・ファイルのセットを読み取り、その内容をOracle Databaseに書き込みます。
インポート対象の表がターゲット・データベースに存在しない場合、またはTABLE_EXISTS_ACTION=REPLACE
を指定する場合、impdp
によってトリガーの作成前に表の作成およびロードが行われるため、トリガーは起動されません。
インポート対象の表がターゲット・データベースに存在し、TABLE_EXISTS_ACTION=APPEND
またはTABLE_EXISTS_ACTION=TRUNCATE
を指定する場合、impdp
によって既存の表に行がロードされ、その表に対して作成されているINSERT
トリガーが起動されます。
関連項目:
データ・ポンプ・インポートの詳細は、『Oracle Databaseユーティリティ』を参照してください。
オリジナル・インポート(imp
)
オリジナル・インポート(オリジナル・インポート・ユーティリティimp
)は、オリジナル・エクスポート(オリジナル・エクスポート・ユーティリティexp
)によって作成されたダンプ・ファイルからオブジェクト定義および表データを読み取ります。
注意:
オリジナル・エクスポートによって作成されたファイルをインポートするには、オリジナル・インポートを使用する必要があります。それ以外のすべての場合、オリジナル・インポートのかわりにデータ・ポンプ・インポートを使用することをお薦めします。
インポート対象の表がターゲット・データベースに存在しない場合、imp
によってトリガーの作成前に表の作成およびロードが行われるため、トリガーは起動されません。
インポート対象の表がターゲット・データベースに存在する場合、インポートのIGNORE
パラメータによってインポート操作中にトリガーが起動されるかどうかが決定されます。IGNORE
パラメータでは、オブジェクト作成エラーを無視するかどうかを指定し、その結果、次のように動作します。
IGNORE=n
(デフォルト)の場合、imp
では表が変更されず、トリガーは起動されません。
IGNORE=y
の場合、imp
によって既存の表に行がロードされ、その表に対して作成されているINSERT
トリガーが起動されます。
関連項目:
オリジナル・インポート・ユーティリティの詳細は、『Oracle Databaseユーティリティ』を参照してください。
オリジナル・エクスポート・ユーティリティの詳細は、『Oracle Databaseユーティリティ』を参照してください。
IGNORE
の詳細は、『Oracle Databaseユーティリティ』を参照してください。
トリガーを使用してイベントをパブリッシュするには、次のようなトリガーを作成します。
トリガーに、トリガーを起動するイベントとしてそのイベントを含めること。
トリガーで、DBMS_AQ
パッケージの適切なサブプログラムを起動すること。このパッケージには、Oracle Streamsアドバンスト・キューイング(AQ)に対するインタフェースが含まれます。
DBMS_AQ
パッケージの詳細は、『Oracle Database PL/SQLパッケージ・プロシージャおよびタイプ・リファレンス』を参照してください。
AQの詳細は、『Oracle Databaseアドバンスト・キューイング・ユーザーズ・ガイド』を参照してください。
このようなトリガーを有効化および無効化することで、イベント通知のオンとオフを切り替えることができます。トリガーの有効化および無効化の詳細は、「トリガーの有効化および無効化」を参照してください。
トリガーによるイベントのパブリッシュ方法
データベースは、イベントを検出すると、そのイベントに対して定義されている有効なすべてのトリガーを起動します。これには次の例外があります。
トリガーがトリガーを起動するイベントのターゲットである場合、このトリガーは起動されません。
たとえば、すべてのDROP
イベントのトリガーは、トリガー自体が削除される場合は起動されません。
変更されたトリガーが、トリガーを起動するイベントと同じトランザクション内でコミットされなかった場合、このトリガーは起動されません。
たとえば、システム・トリガー内の再帰DDL文によって別のトリガーが変更されると、同じトランザクション内のイベントでは、変更されたトリガーを起動できなくなります。
トリガーによってAQが起動されると、AQはイベントをパブリッシュして、トリガーにパブリケーション・コンテキストおよび指定された属性を渡します。トリガーでは、イベント属性ファンクションを起動することでこれらの属性にアクセスできます。
トリガーが(IN
パラメータとしてAQに渡すことで)AQに対して指定し、その後イベント属性ファンクションを使用してアクセスできる属性は、トリガーを起動するイベント(データベース・イベントまたはクライアント・イベント)によって異なります。
注意:
トリガーは、常に定義者権限(DR)ユニットのように動作します。イベントのトリガー・アクションは、アクションの定義者として(コールアウト内のパッケージまたはファンクションの定義者、またはキュー内のトリガーの所有者として)実行されます。トリガーの所有者には基礎となるキュー、パッケージまたはサブプログラムに対するEXECUTE
権限が必要なため、このアクションには一貫性があります。DRユニットの詳細は、「実行者権限および定義者権限(AUTHIDプロパティ)」を参照してください。
データベースでは、すべてのイベントについてコールバック・ファンクションからの戻りステータスが無視されます。たとえば、データベースでは、SHUTDOWN
イベントからの戻りステータスに対して何も実行できません。
ここでのトピック
トリガーでは、表9-5のシステム定義のイベント属性ファンクションを起動することで、トリガーを起動するイベントの特定の属性を取り出すことができます。すべてのトリガーがすべてのイベント属性ファンクションを起動できるわけではありません(詳細は、「データベース・イベント・トリガーのイベント属性ファンクション」および「クライアント・イベント・トリガーのイベント属性ファンクション」を参照してください)。
注意:
以前のリリースでは、これらのファンクションにはSYS
パッケージを介してアクセスする必要がありました。現在、各ファンクションにアクセスするには、そのパブリック・シノニム(表9-5の最初の列のora_
で始まる名前)を使用することをお薦めします。
ファンクション・パラメータora_name_list_t
は、DBMS_STANDARD
パッケージ内で次のように定義されます。
TYPE ora_name_list_t IS TABLE OF VARCHAR2(64);
表9-5 システム定義のイベント属性
属性 | 戻り型および値 | 例 |
---|---|---|
ora_client_ip_address |
|
DECLARE
v_addr VARCHAR2(11);
BEGIN
IF (ora_sysevent = 'LOGON') THEN
v_addr := ora_client_ip_address;
END IF;
END;
/
|
ora_database_name |
|
DECLARE
v_db_name VARCHAR2(50);
BEGIN
v_db_name := ora_database_name;
END;
/
|
ora_des_encrypted_password |
|
IF (ora_dict_obj_type = 'USER') THEN
INSERT INTO event_table
VALUES (ora_des_encrypted_password);
END IF;
|
ora_dict_obj_name |
|
INSERT INTO event_table
VALUES ('Changed object is ' ||
ora_dict_obj_name);
|
ora_dict_obj_name_list ( name_list OUT ora_name_list_t ) |
|
DECLARE
name_list ora_name_list_t;
number_modified PLS_INTEGER;
BEGIN
IF (ora_sysevent='ASSOCIATE STATISTICS') THEN
number_modified :=
ora_dict_obj_name_list(name_list);
END IF;
END;
|
ora_dict_obj_owner |
|
INSERT INTO event_table
VALUES ('object owner is' ||
ora_dict_obj_owner);
|
ora_dict_obj_owner_list ( owner_list OUT ora_name_list_t ) |
|
DECLARE
owner_list ora_name_list_t;
number_modified PLS_INTEGER;
BEGIN
IF (ora_sysevent='ASSOCIATE STATISTICS') THEN
number_modified :=
ora_dict_obj_name_list(owner_list);
END IF;
END;
|
ora_dict_obj_type |
|
INSERT INTO event_table
VALUES ('This object is a ' ||
ora_dict_obj_type);
|
ora_grantee ( user_list OUT ora_name_list_t ) |
|
DECLARE
user_list ora_name_list_t;
number_of_grantees PLS_INTEGER;
BEGIN
IF (ora_sysevent = 'GRANT') THEN
number_of_grantees :=
ora_grantee(user_list);
END IF;
END;
|
ora_instance_num |
|
IF (ora_instance_num = 1) THEN
INSERT INTO event_table VALUES ('1');
END IF;
|
ora_is_alter_column ( column_name IN VARCHAR2 ) |
|
IF (ora_sysevent = 'ALTER' AND
ora_dict_obj_type = 'TABLE') THEN
alter_column := ora_is_alter_column('C');
END IF;
|
ora_is_creating_nested_table |
|
IF (ora_sysevent = 'CREATE' AND
ora_dict_obj_type = 'TABLE' AND
ora_is_creating_nested_table) THEN
INSERT INTO event_table
VALUES ('A nested table is created');
END IF;
|
ora_is_drop_column ( column_name IN VARCHAR2 ) |
|
IF (ora_sysevent = 'ALTER' AND
ora_dict_obj_type = 'TABLE') THEN
drop_column := ora_is_drop_column('C');
END IF;
|
ora_is_servererror ( error_number IN VARCHAR2 ) |
|
IF ora_is_servererror(error_number) THEN
INSERT INTO event_table
VALUES ('Server error!!');
END IF;
|
ora_login_user |
|
SELECT ora_login_user FROM DUAL;
|
ora_partition_pos |
|
-- Retrieve ora_sql_txt into sql_text variable
v_n := ora_partition_pos;
v_new_stmt := SUBSTR(sql_text,1,v_n - 1)
|| ' ' || my_partition_clause
|| ' ' || SUBSTR(sql_text, v_n));
|
ora_privilege_list ( privilege_list OUT ora_name_list_t ) |
|
DECLARE
privilege_list ora_name_list_t;
number_of_privileges PLS_INTEGER;
BEGIN
IF (ora_sysevent = 'GRANT' OR
ora_sysevent = 'REVOKE') THEN
number_of_privileges :=
ora_privilege_list(privilege_list);
END IF;
END;
|
ora_revokee ( user_list OUT ora_name_list_t ) |
|
DECLARE
user_list ora_name_list_t;
number_of_users PLS_INTEGER;
BEGIN
IF (ora_sysevent = 'REVOKE') THEN
number_of_users := ora_revokee(user_list);
END IF;
END;
|
ora_server_error ( position IN PLS_INTEGER ) |
|
INSERT INTO event_table
VALUES ('top stack error ' ||
ora_server_error(1));
|
ora_server_error_depth |
|
n := ora_server_error_depth;
-- Use n with functions such as ora_server_error
|
ora_server_error_msg ( position IN PLS_INTEGER ) |
|
INSERT INTO event_table
VALUES ('top stack error message' ||
ora_server_error_msg(1));
|
ora_server_error_num_params ( position IN PLS_INTEGER ) |
|
n := ora_server_error_num_params(1);
|
ora_server_error_param ( position IN PLS_INTEGER, param IN PLS_INTEGER ) |
|
-- Second %s in "Expected %s, found %s":
param := ora_server_error_param(1,2);
|
ora_sql_txt ( sql_text OUT ora_name_list_t ) |
|
CREATE TABLE event_table (col VARCHAR2(2030)); DECLARE sql_text ora_name_list_t; n PLS_INTEGER; v_stmt VARCHAR2(2000); BEGIN n := ora_sql_txt(sql_text); FOR i IN 1..n LOOP v_stmt := v_stmt || sql_text(i); END LOOP; INSERT INTO event_table VALUES ('text of triggering statement: ' || v_stmt); END; |
ora_sysevent |
|
INSERT INTO event_table
VALUES (ora_sysevent);
|
ora_with_grant_option |
|
IF (ora_sysevent = 'GRANT' AND
ora_with_grant_option = TRUE) THEN
INSERT INTO event_table
VALUES ('with grant option');
END IF;
|
ora_space_error_info ( error_number OUT NUMBER, error_type OUT VARCHAR2, object_owner OUT VARCHAR2, table_space_name OUT VARCHAR2, object_name OUT VARCHAR2, sub_object_name OUT VARCHAR2 ) |
|
IF (ora_space_error_info ( eno,typ,owner,ts,obj,subobj) = TRUE) THEN DBMS_OUTPUT.PUT_LINE('The object '|| obj || ' owned by ' || owner || ' has run out of space.'); END IF; |
脚注 1
位置1は、スタックの先頭です。
表9-6に、イベント属性ファンクションを起動できるデータベース・イベント・トリガーの概要を示します。表9-6のトリガーを起動するイベントの詳細は、「database_event」を参照してください。
表9-6 データベース・イベント・トリガー
トリガー・イベント | トリガーが起動されるタイミング | WHEN条件 | 制限 | トランザクション | 属性ファンクション |
---|---|---|---|---|---|
AFTER STARTUP |
データベースのオープン時。 |
なし |
トリガーではデータベース操作は実行できません。 |
トリガーの起動後、別のトランザクションを開始してコミットします。 |
ora_sysevent ora_login_user ora_instance_num ora_database_name |
BEFORE SHUTDOWN |
サーバーでインスタンスの停止が開始される直前。 これによって、カートリッジを完全に停止できます。インスタンスの異常停止の場合、このトリガーは起動されない場合があります。 |
なし |
トリガーではデータベース操作は実行できません。 |
トリガーの起動後、別のトランザクションを開始してコミットします。 |
ora_sysevent ora_login_user ora_instance_num ora_database_name |
AFTER DB_ROLE_CHANGE |
ロールの変更後最初のデータベースのオープン時。 |
なし |
なし |
トリガーの起動後、別のトランザクションを開始してコミットします。 |
ora_sysevent ora_login_user ora_instance_num ora_database_name |
AFTER SERVERERROR |
条件がある場合、指定したエラーが発生するたびに起動。条件がない場合、任意のエラーが発生するたびに起動。 トリガーは、「database_event」にリストされているエラーに対しては起動されません。 |
|
エラーによって異なります。 |
トリガーの起動後、別のトランザクションを開始してコミットします。 |
ora_sysevent ora_login_user ora_instance_num ora_database_name ora_server_error ora_is_servererror ora_space_error_info |
表9-7に、イベント属性ファンクションを起動できるクライアント・イベント・トリガーの概要を示します。表9-7のトリガーを起動するイベントの詳細は、「ddl_event」および「database_event」を参照してください。
注意:
クライアント・イベント・トリガーがDDL操作(CREATE
OR
REPLACE
TRIGGER
など)のターゲットになった場合、同じトランザクション中に後でこのトリガーを起動することはできません。
表9-7 クライアント・イベント・トリガー
トリガー・イベント | トリガーが起動されるタイミング | WHEN条件 | 制限 | トランザクション | 属性ファンクション |
---|---|---|---|---|---|
BEFORE ALTER AFTER ALTER |
カタログ・オブジェクトの変更時。 |
オブジェクトの型と名前、 |
トリガーは、イベントを生成したオブジェクトに対してDDL操作を実行できません。 他のオブジェクトに対するDDLは、オブジェクトのコンパイル、トリガーの作成、および表の作成、変更、削除に制限されます。 |
カレント・トランザクションでトリガーが起動されます。 |
ora_sysevent ora_login_user ora_instance_num ora_database_name ora_dict_obj_type ora_dict_obj_name ora_dict_obj_owner ora_des_encrypted_password (for ALTER USER events) ora_is_alter_column (for ALTER TABLE events) ora_is_drop_column (for ALTER TABLE events) |
BEFORE DROP AFTER DROP |
カタログ・オブジェクトの削除時。 |
オブジェクトの型と名前、 |
トリガーは、イベントを生成したオブジェクトに対してDDL操作を実行できません。 他のオブジェクトに対するDDLは、オブジェクトのコンパイル、トリガーの作成、および表の作成、変更、削除に制限されます。 |
カレント・トランザクションでトリガーが起動されます。 |
ora_sysevent ora_login_user ora_instance_num ora_database_name ora_dict_obj_type ora_dict_obj_name ora_dict_obj_owner |
BEFORE ANALYZE AFTER ANALYZE |
|
オブジェクトの型と名前、 |
トリガーは、イベントを生成したオブジェクトに対してDDL操作を実行できません。 他のオブジェクトに対するDDLは、オブジェクトのコンパイル、トリガーの作成、および表の作成、変更、削除に制限されます。 |
カレント・トランザクションでトリガーが起動されます。 |
ora_sysevent ora_login_user ora_instance_num ora_database_name ora_dict_obj_name ora_dict_obj_type ora_dict_obj_owner |
BEFORE ASSOCIATE STATISTICS AFTER ASSOCIATE STATISTICS |
|
オブジェクトの型と名前、 |
トリガーは、イベントを生成したオブジェクトに対してDDL操作を実行できません。 他のオブジェクトに対するDDLは、オブジェクトのコンパイル、トリガーの作成、および表の作成、変更、削除に制限されます。 |
カレント・トランザクションでトリガーが起動されます。 |
ora_sysevent ora_login_user ora_instance_num ora_database_name ora_dict_obj_name ora_dict_obj_type ora_dict_obj_owner ora_dict_obj_name_list ora_dict_obj_owner_list |
BEFORE AUDIT AFTER AUDIT BEFORE NOAUDIT AFTER NOAUDIT |
|
オブジェクトの型と名前、 |
トリガーは、イベントを生成したオブジェクトに対してDDL操作を実行できません。 他のオブジェクトに対するDDLは、オブジェクトのコンパイル、トリガーの作成、および表の作成、変更、削除に制限されます。 |
カレント・トランザクションでトリガーが起動されます。 |
ora_sysevent ora_login_user ora_instance_num ora_database_name |
BEFORE COMMENT AFTER COMMENT |
オブジェクトのコメント化時。 |
オブジェクトの型と名前、 |
トリガーは、イベントを生成したオブジェクトに対してDDL操作を実行できません。 他のオブジェクトに対するDDLは、オブジェクトのコンパイル、トリガーの作成、および表の作成、変更、削除に制限されます。 |
カレント・トランザクションでトリガーが起動されます。 |
ora_sysevent ora_login_user ora_instance_num ora_database_name ora_dict_obj_name ora_dict_obj_type ora_dict_obj_owner |
BEFORE CREATE AFTER CREATE |
カタログ・オブジェクトの作成時。 |
オブジェクトの型と名前、 |
トリガーは、イベントを生成したオブジェクトに対してDDL操作を実行できません。 他のオブジェクトに対するDDLは、オブジェクトのコンパイル、トリガーの作成、および表の作成、変更、削除に制限されます。 |
カレント・トランザクションでトリガーが起動されます。 |
ora_sysevent ora_login_user ora_instance_num ora_database_name ora_dict_obj_type ora_dict_obj_name ora_dict_obj_owner ora_is_creating_nested_table (for CREATE TABLE events) |
BEFORE DDL AFTER DDL |
ほとんどのSQL DDL文の発行時。PL/SQLサブプログラム・インタフェースを介して発行された |
オブジェクトの型と名前、 |
トリガーは、イベントを生成したオブジェクトに対してDDL操作を実行できません。 他のオブジェクトに対するDDLは、オブジェクトのコンパイル、トリガーの作成、および表の作成、変更、削除に制限されます。 |
カレント・トランザクションでトリガーが起動されます。 |
ora_sysevent ora_login_user ora_instance_num ora_database_name ora_dict_obj_name ora_dict_obj_type ora_dict_obj_owner |
BEFORE DISASSOCIATE STATISTICS AFTER DISASSOCIATE STATISTICS |
|
オブジェクトの型と名前、 |
トリガーは、イベントを生成したオブジェクトに対してDDL操作を実行できません。 他のオブジェクトに対するDDLは、オブジェクトのコンパイル、トリガーの作成、および表の作成、変更、削除に制限されます。 |
カレント・トランザクションでトリガーが起動されます。 |
ora_sysevent ora_login_user ora_instance_num ora_database_name ora_dict_obj_name ora_dict_obj_type ora_dict_obj_owner ora_dict_obj_name_list ora_dict_obj_owner_list |
BEFORE GRANT AFTER GRANT |
|
オブジェクトの型と名前、 |
トリガーは、イベントを生成したオブジェクトに対してDDL操作を実行できません。 他のオブジェクトに対するDDLは、オブジェクトのコンパイル、トリガーの作成、および表の作成、変更、削除に制限されます。 |
カレント・トランザクションでトリガーが起動されます。 |
ora_sysevent ora_login_user ora_instance_num ora_database_name ora_dict_obj_name ora_dict_obj_type ora_dict_obj_owner ora_grantee ora_with_grant_option ora_privilege_list |
BEFORE LOGOFF |
ユーザー・ログオフの開始時。 |
|
他のオブジェクトに対するDDLは、オブジェクトのコンパイル、トリガーの作成、および表の作成、変更、削除に制限されます。 |
カレント・トランザクションでトリガーが起動されます。 |
ora_sysevent ora_login_user ora_instance_num ora_database_name |
AFTER LOGON |
ユーザーが正常にログオンした後。 |
|
他のオブジェクトに対するDDLは、オブジェクトのコンパイル、トリガーの作成、および表の作成、変更、削除に制限されます。 |
トリガーの起動後、別のトランザクションを開始してコミットします。 |
ora_sysevent ora_login_user ora_instance_num ora_database_name ora_client_ip_address |
BEFORE RENAME AFTER RENAME |
|
オブジェクトの型と名前、 |
トリガーは、イベントを生成したオブジェクトに対してDDL操作を実行できません。 他のオブジェクトに対するDDLは、オブジェクトのコンパイル、トリガーの作成、および表の作成、変更、削除に制限されます。 |
カレント・トランザクションでトリガーが起動されます。 |
ora_sysevent ora_login_user ora_instance_num ora_database_name ora_dict_obj_name ora_dict_obj_owner ora_dict_obj_type |
BEFORE REVOKE AFTER REVOKE |
|
オブジェクトの型と名前、 |
トリガーは、イベントを生成したオブジェクトに対してDDL操作を実行できません。 他のオブジェクトに対するDDLは、オブジェクトのコンパイル、トリガーの作成、および表の作成、変更、削除に制限されます。 |
カレント・トランザクションでトリガーが起動されます。 |
ora_sysevent ora_login_user ora_instance_num ora_database_name ora_dict_obj_name ora_dict_obj_type ora_dict_obj_owner ora_revokee ora_privilege_list |
AFTER SUSPEND |
領域不足状態のためSQL文が一時停止された後。 (トリガーは、文を再開できるようにこの状態を修正する必要があります。) |
オブジェクトの型と名前、 |
トリガーは、イベントを生成したオブジェクトに対してDDL操作を実行できません。 他のオブジェクトに対するDDLは、オブジェクトのコンパイル、トリガーの作成、および表の作成、変更、削除に制限されます。 |
カレント・トランザクションでトリガーが起動されます。 |
ora_sysevent ora_login_user ora_instance_num ora_database_name ora_server_error ora_is_servererror ora_space_error_info |
BEFORE TRUNCATE AFTER TRUNCATE |
オブジェクトの切捨て時。 |
オブジェクトの型と名前、 |
トリガーは、イベントを生成したオブジェクトに対してDDL操作を実行できません。 他のオブジェクトに対するDDLは、オブジェクトのコンパイル、トリガーの作成、および表の作成、変更、削除に制限されます。 |
カレント・トランザクションでトリガーが起動されます。 |
ora_sysevent ora_login_user ora_instance_num ora_database_name ora_dict_obj_name ora_dict_obj_type ora_dict_obj_owner |
*_TRIGGERS
静的データ・ディクショナリ・ビューには、トリガーに関する情報が表示されます。これらのビューの詳細は、『Oracle Databaseリファレンス』を参照してください。
例9-28では、トリガーを作成し、静的データ・ディクショナリ・ビューUSER_TRIGGERS
を2回問い合せています。最初の問合せでは、トリガーのタイプ、トリガーを起動するイベント、およびトリガーが作成されている表の名前を表示し、次の問合せではトリガー本体を表示します。
注意:
例9-28の問合せ結果は、次のSQL*Plusコマンドによって書式設定されました。
COLUMN Trigger_type FORMAT A15 COLUMN Triggering_event FORMAT A16 COLUMN Table_name FORMAT A11 COLUMN Trigger_body FORMAT A50 SET LONG 9999
例9-28 トリガーに関する情報の表示
CREATE OR REPLACE TRIGGER Emp_count AFTER DELETE ON employees DECLARE n INTEGER; BEGIN SELECT COUNT(*) INTO n FROM employees; DBMS_OUTPUT.PUT_LINE('There are now ' || n || ' employees.'); END; / COLUMN Trigger_type FORMAT A15 COLUMN Triggering_event FORMAT A16 COLUMN Table_name FORMAT A11 COLUMN Trigger_body FORMAT A50
問合せ:
SELECT Trigger_type, Triggering_event, Table_name FROM USER_TRIGGERS WHERE Trigger_name = 'EMP_COUNT';
結果:
TRIGGER_TYPE TRIGGERING_EVENT TABLE_NAME --------------- ---------------- ----------- AFTER STATEMENT DELETE EMPLOYEES
問合せ:
SELECT Trigger_body
FROM USER_TRIGGERS
WHERE Trigger_name = 'EMP_COUNT';
結果:
TRIGGER_BODY -------------------------------------------------- DECLARE n INTEGER; BEGIN SELECT COUNT(*) INTO n FROM employees; DBMS_OUTPUT.PUT_LINE('There are now ' || n || ' employees.'); END; 1 row selected.