Plsql-triggers

提供:Dev Guides
移動先:案内検索

PL/SQL-トリガー

この章では、PL/SQLのトリガーについて説明します。 トリガーは保存されたプログラムで、イベントが発生すると自動的に実行または起動されます。 トリガーは、実際には、次のイベントのいずれかに応答して実行されるように書かれています-

  • *データベース操作(DML)*ステートメント(DELETE、INSERT、またはUPDATE)
  • *データベース定義(DDL)*ステートメント(CREATE、ALTER、またはDROP)。
  • データベース操作(SERVERERROR、LOGON、LOGOFF、STARTUP、またはSHUTDOWN)。

トリガーは、イベントが関連付けられているテーブル、ビュー、スキーマ、またはデータベースで定義できます。

トリガーの利点

トリガーは、次の目的のために書くことができます-

  • いくつかの派生列値を自動的に生成する
  • 参照整合性の強制
  • イベントログとテーブルアクセスに関する情報の保存
  • 監査
  • テーブルの同期複製
  • セキュリティ許可を課す
  • 無効なトランザクションの防止

トリガーの作成

トリガーを作成するための構文は次のとおりです-

CREATE [OR REPLACE ] TRIGGER trigger_name
{BEFORE | AFTER | INSTEAD OF }
{INSERT [OR] | UPDATE [OR] | DELETE}
[OF col_name]
ON table_name
[REFERENCING OLD AS o NEW AS n]
[FOR EACH ROW]
WHEN (condition)
DECLARE
   Declaration-statements
BEGIN
   Executable-statements
EXCEPTION
   Exception-handling-statements
END;

どこで、

  • CREATE [OR REPLACE] TRIGGER trigger_name-既存のトリガーを作成するか、_trigger_name_で置き換えます。
  • \ {前|後| INSTEAD OF}-これは、トリガーがいつ実行されるかを指定します。 INSTEAD OF句は、ビューでトリガーを作成するために使用されます。
  • \ {INSERT [OR] |更新[または] | DELETE}-これはDML操作を指定します。
  • [OF col_name]-これは、更新される列名を指定します。
  • [ON table_name]-これは、トリガーに関連付けられたテーブルの名前を指定します。
  • [古いASを新しいASとして参照]-これにより、INSERT、UPDATE、DELETEなどのさまざまなDMLステートメントの新しい値と古い値を参照できます。
  • [FOR EACH ROW]-これは行レベルのトリガーを指定します。つまり、トリガーは影響を受ける各行に対して実行されます。 それ以外の場合、SQLステートメントの実行時にトリガーは1回だけ実行されます。これは、テーブルレベルトリガーと呼ばれます。 *WHEN(条件)-これは、トリガーが起動する行の条件を提供します。 この句は、行レベルのトリガーに対してのみ有効です。

まず、前の章で作成して使用したCUSTOMERSテーブルを使用します-

Select* from customers;

+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  1 | Ramesh   |  32 | Ahmedabad |  2000.00 |
|  2 | Khilan   |  25 | Delhi     |  1500.00 |
|  3 | kaushik  |  23 | Kota      |  2000.00 |
|  4 | Chaitali |  25 | Mumbai    |  6500.00 |
|  5 | Hardik   |  27 | Bhopal    |  8500.00 |
|  6 | Komal    |  22 | MP        |  4500.00 |
+----+----------+-----+-----------+----------+

次のプログラムは、CUSTOMERSテーブルで実行されたINSERTまたはUPDATEまたはDELETE操作に対して起動するcustomersテーブルの*行レベル*トリガーを作成します。 このトリガーは、古い値と新しい値の給与の違いを表示します-

CREATE OR REPLACE TRIGGER display_salary_changes
BEFORE DELETE OR INSERT OR UPDATE ON customers
FOR EACH ROW
WHEN (NEW.ID > 0)
DECLARE
   sal_diff number;
BEGIN
   sal_diff := :NEW.salary  - :OLD.salary;
   dbms_output.put_line('Old salary: ' || :OLD.salary);
   dbms_output.put_line('New salary: ' || :NEW.salary);
   dbms_output.put_line('Salary difference: ' || sal_diff);
END;
/

上記のコードがSQLプロンプトで実行されると、次の結果が生成されます-

Trigger created.

ここで次の点を考慮する必要があります-

  • OLDおよびNEW参照は、テーブルレベルトリガーでは使用できませんが、レコードレベルトリガーで使用できます。
  • 同じトリガーでテーブルをクエリする場合は、AFTERキーワードを使用する必要があります。トリガーは、最初の変更が適用され、テーブルが一貫した状態に戻った後にのみ、テーブルをクエリまたは再度変更できるためです。
  • 上記のトリガーは、テーブルに対するDELETEまたはINSERTまたはUPDATE操作の前に起動するように記述されていますが、単一または複数の操作(たとえば、BEFORE DELETE)でトリガーを記述できます。テーブルでDELETE操作を使用して削除されます。

トリガーのトリガー

CUSTOMERSテーブルでいくつかのDML操作を実行してみましょう。 これは、テーブルに新しいレコードを作成する1つのINSERTステートメントです-

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (7, 'Kriti', 22, 'HP', 7500.00 );

CUSTOMERSテーブルにレコードが作成されると、上記の作成トリガー display_salary_changes が起動され、次の結果が表示されます-

Old salary:
New salary: 7500
Salary difference:

これは新しいレコードであるため、古い給与は使用できず、上記の結果はnullになります。 CUSTOMERSテーブルでもう1つのDML操作を実行してみましょう。 UPDATEステートメントは、テーブル内の既存のレコードを更新します-

UPDATE customers
SET salary = salary + 500
WHERE id = 2;

CUSTOMERSテーブルでレコードが更新されると、上記の作成トリガー display_salary_changes が起動され、次の結果が表示されます-

Old salary: 1500
New salary: 2000
Salary difference: 500