1. データベースPL/SQL言語リファレンス
  2. PL/SQLトリガー

9 PL/SQLトリガー

トリガーは、指定のイベントが発生するたびにOracle Databaseによって自動的に起動されるストアド・プロシージャに似ています。

注意:

データベースは、システム定義のイベントのみ検出できます。独自のイベントは定義できません。

ここでのトピック

9.1 トリガーの概要

トリガーは、ストアド・プロシージャのように、データベースに格納して繰り返し起動できる名前付き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行単位トリガーなど)を使用して呼ばれます。

関連項目:

9.2 トリガーを使用する理由

トリガーを使用して、データベース管理システムをカスタマイズできます。

たとえば、トリガーを使用すると、次の操作を行うことができます。

  • 仮想列の値の自動的な生成

  • イベントのログへの記録

  • 表へのアクセスに関する統計情報の収集

  • ビューに対してDML文が発行された場合の表データの変更

  • 親表と子表が分散データベースの異なるノード上にあるときの参照整合性の実行

  • サブスクライブ元のアプリケーションへのデータベース・イベント、ユーザー・イベントおよびSQL文に関する情報のパブリッシュ

  • 通常の業務時間後における表に対するDML操作の防止

  • 無効なトランザクションの防止

  • 制約では定義できない複雑なビジネス・ルールまたは参照整合性規則の実行(「トリガーと制約の違い」を参照)

注意:

トリガーは、プログラムで構築されるものであり、無効になりやすいため、信頼性の高いセキュリティ・メカニズムではありません。高いセキュリティを確保するには、Oracle Database Vaultを使用してください(『Oracle Database Vault管理者ガイド』を参照)。

トリガーと制約の違い

トリガーと制約のどちらを使用してもデータ入力を制約できますが、両者は大きく異なります。

トリガーは、常に新規データのみに適用されます。たとえば、トリガーによって、DML文がデータベースの列にNULL値を挿入することを防止できますが、列には、トリガーが定義される前またはトリガーが無効な間に挿入されたNULL値が含まれている可能性があります。

制約は、トリガーのように新しいデータのみに適用するか、新しいデータと既存のデータの両方に適用できます。『Oracle Database SQL言語リファレンス』で説明されているように、制約の動作は制約の状態によって異なります。

制約は、同じルールを実行するトリガーに比べて作成しやすく、エラーが発生する可能性が低くなります。ただしトリガーは、制約では定義できない複雑なビジネス・ルールを実行できます。トリガーは、次の場合にのみデータ入力の制約に使用することをお薦めします。

  • 親表と子表が分散データベースの異なるノード上にあるときに参照整合性を実行する場合

  • 制約では定義できない複雑なビジネス・ルールまたは参照整合性規則を実行する場合

関連項目:

9.3 DMLトリガー

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文には、列リストが含まれる場合があります。列リストが含まれていると、トリガーは、指定された列が更新される場合にのみ起動されます。列リストが省略されていると、トリガーは、関連付けられている表のいずれかの列が更新された場合に起動されます。

ここでのトピック

9.3.1 トリガーを起動するDML文を検出する条件述語

DMLトリガーのトリガーを起動するイベントは、トリガーを起動する複数の文で構成できます。複数の文のうちの1つによってトリガーが起動されると、トリガーは、次の条件述語を使用してその文を判断します。

表9-1 条件述語

条件述語 TRUEになる唯一の条件

INSERTING

INSERT文がトリガーを起動した場合。

UPDATING

UPDATE文がトリガーを起動した場合。

UPDATING ('column')

指定した列に影響するUPDATE文がトリガーを起動した場合。

DELETING

DELETE文がトリガーを起動した場合。

条件述語は、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トリガーは、トリガーを起動する文の操作対象がビューにおける特定のネストした表の列の要素である場合にのみ起動されます。このトリガーは、ネストした表の変更される各要素に対して起動されます。

関連項目:

例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 複合トリガーのタイミング部

タイミング セクション

トリガーを起動する文が実行される前

BEFORE STATEMENT

トリガーを起動する文が実行された後

AFTER STATEMENT

トリガーを起動する文の影響を受ける各行の前

BEFORE EACH ROW

トリガーを起動する文の影響を受ける各行の後

AFTER EACH ROW

関連項目:

複合トリガーの構文の詳細は、「CREATE TRIGGER文」を参照してください

複合DMLトリガーに初期化部はありませんが、他のタイミング部の前に実行されるBEFORE STATEMENT部によって、必要な初期化をすべて実行できます。

複合DMLトリガーにBEFORE STATEMENT部もAFTER STATEMENT部もなく、トリガーを起動する文の影響を受ける行もない場合、トリガーは起動されません。

9.3.3.2 複合DMLトリガーの制限

複合DMLトリガーには、「トリガーの制限」の制限に加え、次の制限があります。

  • OLD、NEWおよびPARENTは宣言部、BEFORE STATEMENT部またはAFTER STATEMENT部では使用できません。

  • BEFORE EACH ROW部のみが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句の併用」を参照してください。

関連項目:

FORALL文

使用例: 新しい表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 参照整合性を保証する制約およびトリガー

表 表で宣言する制約 表で作成するトリガー

親

PRIMARY KEYまたはUNIQUE

PRIMARY KEYまたはUNIQUEの値が更新または削除されたときに、対応するFOREIGN KEYの値に対して目的のアクション(RESTRICT、CASCADEまたはSET NULL)が発生することを保証する1つ以上のトリガー。

親表への挿入にアクションは不要です(依存外部キーは存在しないため)。

子

FOREIGN KEY(親と子が同じデータベースに存在する場合)。(データベースでは、分散データベースの異なるノード上に存在する表の間の宣言参照制約はサポートされません。)

この外部キー制約を無効化して、対応するPRIMARY KEY制約またはUNIQUE制約が削除されることを防止します(CASCADEオプションを使用して明示的に削除する場合を除く)。

FOREIGN KEYで挿入または更新される値が親表のPRIMARY KEYまたはUNIQUEの値に対応することを保証する1つのトリガー。

ここでのトピック

注意:

後続のトピックの例では、列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を使用すると、ユーザーのアクセスを厳密に制御する独自の規則を作成できます。

関連項目:

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

注意:

このトピックの内容は、行レベルで起動されるトリガーにのみ適用されます。つまり、次のようになります。

  • 行レベルの単純な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フィールド値

INSERT

NULL

挿入後の値

UPDATE

更新前の値

更新後の値

DELETE

削除前の値

NULL

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

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

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

    :NEW := NULL;

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

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

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

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

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

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

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

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

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

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

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

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

ログ表の作成:

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

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

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

EMPLOYEES.SALARYの更新: 

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

結果:

2 rows updated.

ログ表の表示:

SELECT * FROM Emp_log;

結果:

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

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

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

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

問合せ:

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

結果:

LAST_NAME                 DEPARTMENT_ID     SALARY JOB_ID
------------------------- ------------- ---------- ----------
Whalen                               10       4200 AD_ASST
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列の変更

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

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

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

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

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

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

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

SELECT * FROM tab1;

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

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

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

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

9.4.1 OBJECT_VALUE疑似列

オブジェクト表に対するDMLトリガーでは、オブジェクト表の列のシステム生成名を戻す、SQL擬似列OBJECT_VALUEを参照できます。トリガーでは、OBJECT_VALUEのデータ型を持つIN仮パラメータを含むPL/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

9.5 システム・トリガー

システム・トリガーは、スキーマまたはデータベースのいずれかに対して作成されます。

このトリガーを起動するイベントは、DDL文(「ddl_event」のリストを参照)またはデータベース操作文(「database_event」のリストを参照)で構成されます。

システム・トリガーは、次のいずれかのタイミングで起動されます。

  • トリガーを起動する文が実行される前

    (このトリガーは、BEFORE文トリガーまたは文レベルのBEFOREトリガーと呼ばれます。)

  • トリガーを起動する文が実行された後

    (このトリガーは、AFTER文トリガーまたは文レベルのAFTERトリガーと呼ばれます。)

  • CREATE文を起動するかわり

    (このトリガーは、INSTEAD OF CREATEトリガーと呼ばれます。)

ここでのトピック

9.5.1 SCHEMAトリガー

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;
/

9.5.2 DATABASEトリガー

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;
/

9.5.3 INSTEAD OF CREATEトリガー

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;
/

9.6 トリガーによって起動されるサブプログラム

トリガーでは、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.jdbc.NUMBER, oracle.jdbc.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.jdbc.*
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;
   }
}

9.7 トリガーのコンパイル、無効化および再コンパイル

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文」を参照)。

9.8 トリガーでの例外処理

ほとんどの場合、例外を呼び出す文がトリガーによって実行され、その例外が例外ハンドラによって処理されないと、データベースは、トリガーとそのトリガーを呼び出す文の両方の影響をロールバックします。

次の場合、データベースは、トリガーを起動する文の影響ではなく、トリガーの影響のみをロールバックします(また、エラーをトレース・ファイルおよびアラート・ログに記録します)。

  • トリガーを起動するイベントが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;
/

9.9 トリガー設計のガイドライン

  • トリガーを使用して、特定のイベントが発生するたびに(どのユーザーまたはアプリケーションがトリガーを起動する文を発行するかにかかわらず)必要なアクションが実行されることを保証します。

    たとえば、トリガーを使用して、いずれかのユーザーが表を更新するたびにそのログ・ファイルが更新されることを保証します。

  • データベース機能が重複するトリガーは作成しないでください。

    たとえば、制約で同じ処理ができるときに、無効なデータを拒否するためのトリガーは作成しないでください(「トリガーと制約の違い」を参照)。

  • 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の値を参照できます。

9.10 トリガーの制限

すべてのPL/SQLユニットに適用される制限に加えて(表C-1を参照)、トリガーには次の制限があります。

関連項目:

「複合DMLトリガーの制限」

9.10.1 トリガーのサイズ制限

トリガーのサイズは、32K以下にしてください。

トリガーのロジックで60行をはるかに超えるPL/SQLソース・テキストが必要な場合は、そのソース・テキストの大部分をストアド・サブプログラムに組み込んで、トリガーからそのサブプログラムを起動してください。トリガーによって起動されるサブプログラムの詳細は、「トリガーによって起動されるサブプログラム」を参照してください。

9.10.2 トリガーのLONGおよびLONG RAWデータ型の制約

注意:

Oracleは、既存アプリケーションとの下位互換性のためにのみ、LONGおよびLONG RAWデータ型をサポートしています。

すべてのPL/SQLユニットに適用される制限に加えて(「LONGおよびLONG RAW変数」を参照)、トリガーには次の制限があります。

  • トリガーでは、LONGまたはLONG RAWデータ型の変数を宣言できません。

  • トリガー内のSQL文では、列データをCHARまたはVARCHAR2データ型に変換できる場合にのみ、LONGまたはLONG RAW列を参照できます。

  • トリガーでは、LONGまたはLONG RAW列で相関名のNEWまたはPARENTを使用できません。

9.10.3 変更表の制限

注意:

このトピックの内容は、行レベルの単純な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

9.11 トリガーの起動順序

タイミングの異なる2つ以上のトリガーを同じ表の同じ文に定義する場合、それらのトリガーは次の順序で起動されます。

  1. すべてのBEFORE STATEMENTトリガー

  2. すべてのBEFORE EACH ROWトリガー

  3. すべてのAFTER EACH ROWトリガー

  4. すべての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リファレンス』を参照)を使用します。

9.12 トリガーの有効化および無効化

デフォルトでは、トリガーは、CREATE TRIGGER文によって有効な状態で作成されます。無効な状態でトリガーを作成するには、DISABLEを指定します。無効な状態でトリガーを作成すると、有効化する前にトリガーがエラーなしでコンパイルされることを確認できます。

次の理由がある場合、トリガーを一時的に無効にできます。

  • トリガーが、使用できないオブジェクトを参照している場合。

  • 大規模なデータ・ロードを実行する必要があり、トリガーを起動せずに迅速に処理する場合。

  • データをリロードする場合。

単一のトリガーを有効化または無効化するには、次の文を使用します。

ALTER TRIGGER [schema.]trigger_name { ENABLE | DISABLE };

特定の表に対して作成されたすべてのエディションのすべてのトリガーを有効化または無効化するには、次の文を使用します。

ALTER TABLE table_name { ENABLE | DISABLE } ALL TRIGGERS;

前述の2つの文で、schemaは、トリガーを含むスキーマの名前で、デフォルトは自分のスキーマです。

関連項目:

9.13 トリガーの変更およびデバッグ

トリガーを変更するには、そのトリガーを置換または再作成する必要があります。(ALTER TRIGGER文は、トリガーの有効化、無効化、コンパイルまたは名前変更のみを実行します。)

トリガーを置換するには、OR REPLACE句を指定してCREATE TRIGGER文を使用します。

トリガーを再作成するには、最初にトリガーをDROP TRIGGER文を使用して削除してから、CREATE TRIGGER文を使用して再作成します。

トリガーをデバッグする場合、ストアド・サブプログラムで使用可能な機能を使用できます。これらの機能の詳細は、『Oracle Database開発ガイド』を参照してください。

関連項目:

9.14 トリガーおよびOracle Databaseデータ転送ユーティリティ

データベースにデータを転送する(トリガーを起動する可能性のある)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トリガーが起動されます。

    関連項目:

9.15 イベントをパブリッシュするトリガー

トリガーを使用してイベントをパブリッシュするには、次のようなトリガーを作成します。

このようなトリガーを有効化および無効化することで、イベント通知のオンとオフを切り替えることができます。トリガーの有効化および無効化の詳細は、「トリガーの有効化および無効化」を参照してください。

トリガーによるイベントのパブリッシュ方法

データベースは、イベントを検出すると、そのイベントに対して定義されている有効なすべてのトリガーを起動します。これには次の例外があります。

  • トリガーがトリガーを起動するイベントのターゲットである場合、このトリガーは起動されません。

    たとえば、すべてのDROPイベントのトリガーは、トリガー自体が削除される場合は起動されません。

  • 変更されたトリガーが、トリガーを起動するイベントと同じトランザクション内でコミットされなかった場合、このトリガーは起動されません。

    たとえば、システム・トリガー内の再帰DDL文によって別のトリガーが変更されると、同じトランザクション内のイベントでは、変更されたトリガーを起動できなくなります。

トリガーによってAQが起動されると、AQはイベントをパブリッシュして、トリガーにパブリケーション・コンテキストおよび指定された属性を渡します。トリガーでは、イベント属性ファンクションを起動することでこれらの属性にアクセスできます。

トリガーが(INパラメータとしてAQに渡すことで)AQに対して指定し、その後イベント属性ファンクションを使用してアクセスできる属性は、トリガーを起動するイベント(データベース・イベントまたはクライアント・イベント)によって異なります。

注意:

  • トリガーは、常に定義者権限(DR)ユニットのように動作します。イベントのトリガー・アクションは、アクションの定義者として(コールアウト内のパッケージまたはファンクションの定義者、またはキュー内のトリガーの所有者として)実行されます。トリガーの所有者には基礎となるキュー、パッケージまたはサブプログラムに対するEXECUTE権限が必要なため、このアクションには一貫性があります。DRユニットの詳細は、「実行者権限および定義者権限(AUTHIDプロパティ)」を参照してください。

  • データベースでは、すべてのイベントについてコールバック・ファンクションからの戻りステータスが無視されます。たとえば、データベースでは、SHUTDOWNイベントからの戻りステータスに対して何も実行できません。

ここでのトピック

9.15.1 イベント属性ファンクション

トリガーでは、表9-5のシステム定義のイベント属性ファンクションを起動することで、トリガーを起動するイベントの特定の属性を取り出すことができます。すべてのトリガーがすべてのイベント属性ファンクションを起動できるわけではありません(詳細は、「データベース・イベント・トリガーのイベント属性ファンクション」および「クライアント・イベント・トリガーのイベント属性ファンクション」を参照してください)。

注意:

  • 以前のリリースでは、これらのファンクションにはSYSパッケージを介してアクセスする必要がありました。現在、各ファンクションにアクセスするには、そのパブリック・シノニム(表9-5の最初の列のora_で始まる名前)を使用することをお薦めします。

  • ファンクション・パラメータora_name_list_tは、DBMS_STANDARDパッケージ内で次のように定義されます。 

    TYPE ora_name_list_t IS TABLE OF VARCHAR2(2*(ORA_MAX_NAME_LEN+2)+1);

表9-5 システム定義のイベント属性

属性 戻り型および値 例 
ora_client_ip_address

VARCHAR2: 基礎となるプロトコルがTCP/IPの場合、LOGONイベントでのクライアントのIPアドレス 

DECLARE
  v_addr VARCHAR2(11);
BEGIN
  IF (ora_sysevent = 'LOGON') THEN
    v_addr := ora_client_ip_address;
  END IF;
END;
/
 
ora_database_name

VARCHAR2(50): データベース名 

DECLARE
  v_db_name VARCHAR2(50);
BEGIN
  v_db_name := ora_database_name;
END;
/
 
ora_des_encrypted_password

VARCHAR2: 作成または変更されるユーザーのDES暗号化パスワード 

IF (ora_dict_obj_type = 'USER') THEN
  INSERT INTO event_table
  VALUES (ora_des_encrypted_password);
END IF;
 
ora_dict_obj_name

VARCHAR2(128): DDL操作が発生したディクショナリ・オブジェクトの名前 

INSERT INTO event_table 
VALUES ('Changed object is ' ||
        ora_dict_obj_name);
 
ora_dict_obj_name_list (
name_list OUT ora_name_list_t
)

PLS_INTEGER: イベントで変更されたオブジェクト名の数

OUTパラメータ: イベントで変更されたオブジェクト名のリスト 

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

VARCHAR2(128): DDL操作が発生したディクショナリ・オブジェクトの所有者 

INSERT INTO event_table
VALUES ('object owner is' || 
        ora_dict_obj_owner);
 
ora_dict_obj_owner_list (
owner_list OUT ora_name_list_t
)

PLS_INTEGER: イベントで変更されたオブジェクトの所有者の数

OUTパラメータ: イベントで変更されたオブジェクトの所有者のリスト 

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

VARCHAR2(20): DDL操作が発生したディクショナリ・オブジェクトの型 

INSERT INTO event_table
VALUES ('This object is a ' || 
        ora_dict_obj_type);
 
ora_grantee (
user_list OUT ora_name_list_t
)

PLS_INTEGER: 権限付与イベントでの権限受領者の数

OUTパラメータ: 権限付与イベントでの権限受領者のリスト 

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

NUMBER: インスタンス番号 

IF (ora_instance_num = 1) THEN
  INSERT INTO event_table VALUES ('1');
END IF;
 
ora_is_alter_column (
column_name IN VARCHAR2
)

BOOLEAN: 指定した列が変更されている場合はTRUE、それ以外の場合はFALSE 

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

BOOLEAN: 現行のイベントがネストした表を作成している場合はTRUE、それ以外の場合はFALSE 

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
)

BOOLEAN: 指定した列が削除されている場合はTRUE、それ以外の場合はFALSE 

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
)

BOOLEAN: 指定したエラーがエラー・スタック上にある場合はTRUE、それ以外の場合はFALSE 

IF ora_is_servererror(error_number) THEN
  INSERT INTO event_table
  VALUES ('Server error!!');
END IF;
 
ora_login_user

VARCHAR2(128): ログイン・ユーザー名 

SELECT ora_login_user FROM DUAL;
 
ora_partition_pos

PLS_INTEGER: CREATE TABLEのINSTEAD OFトリガーでは、PARTITION句を挿入できるSQLテキスト内の位置 

-- 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
)

PLS_INTEGER: 権限付与イベントまたは取消しイベントでの権限の数

OUTパラメータ: イベントで付与または取り消された権限のリスト 

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
)

PLS_INTEGER: 権限取消しイベントでの取消し対象者の数

OUTパラメータ: イベントでの権限取消し対象者のリスト 

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
)

NUMBER: エラー・スタック上の指定した位置にあるエラー・コード脚注 1

 
INSERT INTO event_table
VALUES ('top stack error ' || 
        ora_server_error(1));
 
ora_server_error_depth

PLS_INTEGER: エラー・スタック上のエラー・メッセージの数 

n := ora_server_error_depth;
-- Use n with functions such as ora_server_error
 
ora_server_error_msg (
position IN PLS_INTEGER
)

VARCHAR2: エラー・スタック上の指定した位置にあるエラー・メッセージ脚注 1

 
INSERT INTO event_table
VALUES ('top stack error message' ||
        ora_server_error_msg(1));
 
ora_server_error_num_params (
position IN PLS_INTEGER
)

PLS_INTEGER: エラー・スタック上の指定した位置にある(%sなどの書式を使用して)エラー・メッセージに置き換えられた文字列の数脚注 1

 
n := ora_server_error_num_params(1);
 
ora_server_error_param (
position IN PLS_INTEGER,
param IN PLS_INTEGER
)

VARCHAR2: 指定した位置およびパラメータ番号におけるエラー・メッセージ内の一致する置換値(%s、%dなど)脚注 1

 
-- Second %s in "Expected %s, found %s":
param := ora_server_error_param(1,2);
 
ora_sql_txt (
sql_text OUT ora_name_list_t
)

PLS_INTEGER: PL/SQL表での要素の数

OUTパラメータ: トリガーを起動する文のSQLテキスト(文が長い場合、複数のコレクション要素に分割) 

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

VARCHAR2(20): 構文に指定されたトリガーを起動するイベントの名前 

INSERT INTO event_table
VALUES (ora_sysevent);
 
ora_with_grant_option

BOOLEAN: GRANTオプションとともに権限が付与されている場合はTRUE、それ以外の場合はFALSE 

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
)

BOOLEAN: エラーが領域不足状態に関連している場合はTRUE、それ以外の場合はFALSE

OUTパラメータ: エラーの原因となったオブジェクトに関する情報 

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.15.2 データベース・イベント・トリガーのイベント属性ファンクション

表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」にリストされているエラーに対しては起動されません。

ERRNO = eno

エラーによって異なります。

トリガーの起動後、別のトランザクションを開始してコミットします。

 
ora_sysevent
ora_login_user
ora_instance_num
ora_database_name
ora_server_error
ora_is_servererror
ora_space_error_info

9.15.3 クライアント・イベント・トリガーのイベント属性ファンクション

表9-7に、イベント属性ファンクションを起動できるクライアント・イベント・トリガーの概要を示します。表9-7のトリガーを起動するイベントの詳細は、「ddl_event」および「database_event」を参照してください。

注意:

クライアント・イベント・トリガーがDDL操作(CREATE OR REPLACE TRIGGERなど)のターゲットになった場合、同じトランザクション中に後でこのトリガーを起動することはできません。

表9-7 クライアント・イベント・トリガー

トリガー・イベント トリガーが起動されるタイミング WHEN条件 制限事項 トランザクション 属性ファンクション 
BEFORE ALTER

AFTER ALTER

カタログ・オブジェクトの変更時。

オブジェクトの型と名前、UIDおよびUSERに関する単純な条件

トリガーは、イベントを生成したオブジェクトに対して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

カタログ・オブジェクトの削除時。

オブジェクトの型と名前、UIDおよびUSERに関する単純な条件

トリガーは、イベントを生成したオブジェクトに対して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

ANALYZE文の発行時。

オブジェクトの型と名前、UIDおよびUSERに関する単純な条件

トリガーは、イベントを生成したオブジェクトに対して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

ASSOCIATE STATISTICS文の発行時。

オブジェクトの型と名前、UIDおよびUSERに関する単純な条件

トリガーは、イベントを生成したオブジェクトに対して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

AUDITまたはNOAUDIT文の発行時。

オブジェクトの型と名前、UIDおよびUSERに関する単純な条件

トリガーは、イベントを生成したオブジェクトに対してDDL操作を実行できません。

他のオブジェクトに対するDDLは、オブジェクトのコンパイル、トリガーの作成、および表の作成、変更、削除に制限されます。

カレント・トランザクションでトリガーが起動されます。

 
ora_sysevent
ora_login_user
ora_instance_num
ora_database_name
 
BEFORE COMMENT

AFTER COMMENT

オブジェクトのコメント化時。

オブジェクトの型と名前、UIDおよびUSERに関する単純な条件

トリガーは、イベントを生成したオブジェクトに対して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

カタログ・オブジェクトの作成時。

オブジェクトの型と名前、UIDおよびUSERに関する単純な条件

トリガーは、イベントを生成したオブジェクトに対して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サブプログラム・インタフェースを介して発行されたALTER DATABASE、CREATE CONTROLFILE、CREATE DATABASEおよびDDL(アドバンスト・キューの作成など)に対しては起動されません。

オブジェクトの型と名前、UIDおよびUSERに関する単純な条件

トリガーは、イベントを生成したオブジェクトに対して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

DISASSOCIATE STATISTICS文の発行時。

オブジェクトの型と名前、UIDおよびUSERに関する単純な条件

トリガーは、イベントを生成したオブジェクトに対して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

GRANT文の発行時。

オブジェクトの型と名前、UIDおよびUSERに関する単純な条件

トリガーは、イベントを生成したオブジェクトに対して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

ユーザー・ログオフの開始時。

UIDおよびUSERに関する単純な条件

他のオブジェクトに対するDDLは、オブジェクトのコンパイル、トリガーの作成、および表の作成、変更、削除に制限されます。

カレント・トランザクションでトリガーが起動されます。

 
ora_sysevent
ora_login_user
ora_instance_num
ora_database_name
 
AFTER LOGON

ユーザーが正常にログオンした後。

UIDおよびUSERに関する単純な条件

他のオブジェクトに対するDDLは、オブジェクトのコンパイル、トリガーの作成、および表の作成、変更、削除に制限されます。

トリガーの起動後、別のトランザクションを開始してコミットします。

 
ora_sysevent
ora_login_user
ora_instance_num
ora_database_name
ora_client_ip_address
 
BEFORE RENAME

AFTER RENAME

RENAME文の発行時。

オブジェクトの型と名前、UIDおよびUSERに関する単純な条件

トリガーは、イベントを生成したオブジェクトに対して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

REVOKE文の発行時。

オブジェクトの型と名前、UIDおよびUSERに関する単純な条件

トリガーは、イベントを生成したオブジェクトに対して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文が一時停止された後。

(トリガーは、文を再開できるようにこの状態を修正する必要があります。)

オブジェクトの型と名前、UIDおよびUSERに関する単純な条件

トリガーは、イベントを生成したオブジェクトに対して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

オブジェクトの切捨て時。

オブジェクトの型と名前、UIDおよびUSERに関する単純な条件

トリガーは、イベントを生成したオブジェクトに対して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

9.16 トリガーに関する情報が表示されるビュー

*_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.