【ORACLE】トリガーを作成・変更・削除するSQL

ここではORACLEデータベースでトリガー(trigger)を作成・変更・削除する方法を紹介します。

トリガーの一覧や定義を確認する方法は↓で紹介していますので参考にしてください。
＞＞【ORACLE】トリガーの一覧や定義を確認するSQL

トリガーとは

トリガーとは、あるテーブルに対して操作（登録・更新・削除）が行われたときに、特定の処理を実行するプログラムです。

例えば、社員マスタに変更があったときに、変更の履歴を別のテーブルに記録することが出来ます。他にもAという表に変更があったときに、Bという表も同時に変更するといった使い方が出来ます。

ただし、バックグラウンドで実行されるので、トリガーを多用するとどんな処理が実行されているのか分からなくなることがあります。そのためトリガーの利用を避けているシステムもあります。

トリガーを作成するSQL

トリガーの作成はCREATE TARIGGER文を使います。

トリガーの作成や変更、削除には↓の権限が必要です。
　作成：CREATE TRIGGER、CREATE ANY TRIGGER
　変更：ALTER ANY TRIGGER
　削除：DROP ANY TRIGGER

システム権限の付与の方法については↓で紹介していますので参考にしてください。
＞＞【ORACLE】システム権限を付与・取消するSQL

CREATE TRIGGER文の基本構文

CREATE TRIGGERの基本構文は次の通りです。

CREATE [OR REPLACE] TRIGGER トリガー名
[BEFORE | AFTER | INSTEAD OF] [INSERT | UPDATE | DELETE] ON テーブル名
[FOR EACH ROW]
BEGIN
 トリガーの処理内容;
END;

・[OR REPLACE]で上書きされます。

・[BEFORE | AFTER | INSTEAD OF]でトリガーの起動タイミングを指定します。
　BEFORE：表に対する操作が行われる前にトリガーを実行
　AFTER：表に対する操作が終わった後にトリガーを実行

・[FOR EACH ROW]を指定すると、データ操作された行数分、トリガーの処理が実行される。例えば100行UPDATEされた場合は100回分トリガーの処理が実行される。

CREATE TRIGGER文のSQLサンプル

CREATE TRIGGERのSQLサンプルを紹介します。

次のTRIGGERでは、TAB1テーブルのレコードが削除されたとき、TAB2テーブルへレコードをINSERTしています。

CREATE OR REPLACE TRIGGER trg1
AFTER DELETE ON tab1
FOR EACH ROW
BEGIN
 INSERT INTO tab2 values('tab1','DELETE',sysdate);
END;

表に対する操作がINSERT、UPDATE、DELETEによって条件分岐させて、トリガーの処理を実行したい場合は次のように書きます。

次のSQLでは、INSERT、UPDATE、DELETEごとに別の値をTAB2テーブルへINSERTしています。

CREATE OR REPLACE TRIGGER trg1
AFTER INSERT OR UPDATE OR DELETE ON tab1
FOR EACH ROW
BEGIN
 IF inserting then
  INSERT INTO tab2 values('tab1','INSERT',sysdate)
 elseif updating then
  INSERT INTO tab2 values('tab1','UPDATE',sysdate)
 elseif deleting then
  INSERT INTO tab2 values('tab1','DELETE',sysdate)
 END IF;
END;

ログオントリガー

ユーザーのログオンを契機にトリガー(trigger)を起動する方法をログオントリガーと呼びます。

ログオントリガーは、トリガーの起動タイミングを「AFTER LOGON」とすることで作成できます。

「ON DATABASE」でデータベース全体に対するログオン操作を対象としています。

次のSQLでは、データベースにユーザーがログインすると。TAB1テーブルにユーザ名と操作、日付を登録しています。

CREATE OR REPLACE TRIGGER trg1
AFTER LOGON ON DATABASE
BEGIN
 INSERT INTO tab1 values(ORA_LOGIN_USER,'LOGON',sysdate);
END;

ログアウトトリガー

ログオントリガーと同じようにして、ログアウトトリガーも作成することが出来ます。ログアウトトリガーの起動タイミングは「AFTER LOOFF」とします。

「ON DATABASE」でデータベース全体に対するログオン操作を対象としています。

次のSQLでは、データベースにユーザーがログアウトすると。TAB1テーブルにユーザ名と操作、日付を登録しています。

CREATE OR REPLACE TRIGGER trg1
AFTER LOGOFF ON DATABASE
BEGIN
 INSERT INTO tab1 values(ORA_LOGIN_USER,'LOGOFF',sysdate);
END;

トリガーを有効・無効に変更するSQL

ALTER文を使うことでトリガーを有効、または無効に変更することが出来ます。

--有効にする方法
ALTER TRIGGER trg1 ENABLE;

--無効にする方法
ALTER TRIGGER trg1 DISABLE;

ある表に対するトリガーを全て有効または無効に変更するにはALTER TABLE文を使います。

--有効にする方法
ALTER TABLE sqles ENABLE ALL TRIGGERS;

--無効にする方法
ALTER TABLE sqles DISABLE ALL TRIGGERS;

トリガーを削除するSQL

トリガーを削除するときはDROP文を使います。

DROP TRIGGER trg1;

まとめ

トリガーの作成はCREATE TRIGGER文、変更はALTER文、削除はDROP文を使います。

トリガーの作成や変更、削除には↓の権限が必要です。
　作成：CREATE TRIGGER、CREATE ANY TRIGGER
　変更：ALTER ANY TRIGGER
　削除：DROP ANY TRIGGER

システム権限の付与の方法については↓で紹介していますので参考にしてください。
＞＞【ORACLE】システム権限を付与・取消するSQL