データベーストリガー（Oracle）

ITコーディネータのシュウです。

桜の時期も終わってしまいました。ただ、今年は桜が咲いてから、少し寒かったので、桜がすぐには散らず、例年より長く、桜を楽しむことができたように思います。
東北や北海道などは、まだこれからがいよいよ本番ということなので、仕事で出張とかあればついでにお花見もできるのですが、そういう機会があるかな～。

＜本日の題材＞
データベーストリガー（Oracle）

データベーストリガーについて、まだブログで取り上げていなかったので、今回、取り上げてみたいと思います。
データベーストリガーとは、プロシージャやファンクションと同じように、データベースに格納されたPL/SQLのプログラムですが、プログラムなどから明示的にコールされて呼び出すのではなく、何らかのイベントをきっかけとして自動的に起動されるものです。

トリガーには、大きくは「DMLトリガー」「DDLトリガー」「その他」の3つの種類があります。
「DMLトリガー」は、特定の表に対してDML操作(INSERT, UPDATE, DELETEなど)が行われるときに起動するもの
「DDLトリガー」は、DDL操作(CREATE, ALTER, DROPなど)により起動するもの
「その他」については、
　・データベースにログインやログオフするとき
　・データベースの起動時や停止時
　・エラーの発生時
などで起動するものがあります。
一般的によく使用される「DMLトリガー」についてですが、文トリガー、行トリガーという種類があり、さらに、処理の前後のどちらで起動させるかという、「BEFOREトリガー」「AFTERトリガー」という分類があります。

例）
今回は、ユーザー管理用の「ACCOUNTS」テーブルというものがあったとして、それに対しての、INSERT, UPDATE, DELETE が行われるタイミング(AFTER)で、処理のログを「ACCOUNTS_LOG」テーブルに登録するトリガーを作成してみます。

まず、処理のログを登録する「ACCOUNTS_LOG」テーブルを作成します。

CREATE TABLE accounts_log(
log_date DATE
,old_account_id INT
,new_account_id INT
,action VARCHAR2(50));

次に、トリガーを作成しますが、「ACCOUNTS」テーブルに対しての、INSERT, UPDATE, DELETE が行われてデータが更新されるたびに、「ACCOUNTS_LOG」テーブルに、処理日、変更前後の「ACCOUNT_ID」、そして処理内容を記録させます。
今回は、行トリガーのAFTERトリガーとして作成します。

CREATE OR REPLACE TRIGGER accounts_change_trigger
AFTER INSERT OR UPDATE OR DELETE
ON accounts FOR EACH ROW
DECLARE
log_action accounts_log.action%TYPE;
BEGIN
IF INSERTING THEN
log_action := 'Insert';
ELSIF UPDATING THEN
log_action := 'Update';
ELSIF DELETING THEN
log_action := 'Delete';
ELSE
DBMS_OUTPUT.PUT_LINE('This code is not reachable.');
END IF;

INSERT INTO accounts_log (log_date, old_account_id, new_account_id, action)
VALUES (SYSDATE, :OLD.account_id, :NEW.account_id, log_action);
END;
/

トリガーの中で、「FOR EACH ROW」を付けることで、行トリガーであることを示しています。そして、:OLD.列名、:NEW.列名とすることで、変更前と変更後の列名の値を取得することができます。
それでは、下記のように、「ACCOUNTS」テーブルへのデータの登録、修正、削除を順に行ってみます。

INSERT INTO accounts(account_id, name) VALUES(101, '山田　太郎');

INSERT INTO accounts(account_id, name) VALUES(102, '高橋　三郎');

UPDATE accounts SET NAME = '高橋　一郎' WHERE account_id = 102;

DELETE FROM accounts WHERE account_id = 101;

それでは、「ACCOUNTS_LOG」テーブルを確認してみます。

「LOG_DATE」項目の表示を時刻まで出すようにフォーマットを変更します。
ALTER SESSION SET NLS_DATE_FORMAT='YYYY/MM/DD HH24:MI:SS';

データを確認します。
SELECT * FROM accounts_log
ORDER BY log_date;

たしかに、「ACCOUNTS_LOG」テーブルへの更新履歴が登録されています。
ここで、INSERTの場合は、変更前はデータがないので、old_accounti_id は NULL であり、DELETEの場合は、変更後はデータがなくなるので、new_accounti_id は NULLになることがわかります。

また、COMMITをする前にROLLBACKをしてみると「ACCOUNTS_LOG」のデータはどうなるでしょうか？

ROLLBACK;