PL / SQL-トリガー

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

A database manipulation (DML) ステートメント（DELETE、INSERT、またはUPDATE）
A database definition (DDL) ステートメント（CREATE、ALTER、またはDROP）。
A database operation （SERVERERROR、LOGON、LOGOFF、STARTUP、またはSHUTDOWN）。

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

トリガーの利点

トリガーは次の目的で作成できます-

一部の派生列値を自動的に生成する
参照整合性の実施
イベントログとテーブルアクセスに関する情報の保存
Auditing
テーブルの同期レプリケーション
セキュリティ認証を課す
無効なトランザクションの防止

トリガーの作成

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

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] TRIGGERtrigger_name-既存のトリガーを作成するか、trigger_nameで置き換えます。
{前| 後| INSTEAD OF}-これは、トリガーがいつ実行されるかを指定します。INSTEAD OF句は、ビューにトリガーを作成するために使用されます。
{INSERT [OR] | 更新[または] | DELETE}-これはDML操作を指定します。
[OF col_name] −これは更新される列名を指定します。
[ON table_name]-これは、トリガーに関連付けられたテーブルの名前を指定します。
[古いものを新しいものとして参照する]-これにより、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 | 
+----+----------+-----+-----------+----------+

次のプログラムは、 row-levelCUSTOMERSテーブルで実行された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