[mysql]トリガ作成例

  備忘録
【注意】
この記事は過去に別サイトで執筆したものを再掲載したものです。
内容が古くなっていて現在では適用できない場合や、もっと良い方法が出来ている場合があります。
　

トリガの書式

テーブル作成のようにcreateやdropコマンドを用いる。

CREATE
    [DEFINER = { user | CURRENT_USER }]
    TRIGGER trigger_name trigger_time trigger_event
    ON tbl_name FOR EACH ROW trigger_stmt
DROP TRIGGER [IF EXISTS] [schema_name.]trigger_name
  • trigger_time には、AFTER(事後)／BEFORE(事前)を指定する。
  • trigger_event は、有効化するステートメントの種類を指定する。
    • INSERT指定：insertやreplaceなどで新しい行が挿入されたとき。
    • UPDATE指定：updateなどで行が修正されたとき。
    • DELETE指定：deleteやreplaceで行が削除されたとき。truncateやdrop tableは除く。
  • 同じtrigger_time、trigger_eventで複数の処理を登録することは出来ない。

自動的に変更ログを取る方法

あるテーブルへの変更をトリガ機能を使って逐一ログを取には。

準備

説明用テーブル構成、アドレス帳っぽいものを想定。

カラム名データ型備考
idintegerPK
namevarchar(30)
addressvarchar(255)
upd_usrvarchar(10)
table_x

ログ用テーブルはほとんど同じ構成で操作日時や操作者を記録できるようにしておく。また、PK指定は外しておく。

カラム名データ型備考
idintegerPK指定しない
namevarchar(30)
addressvarchar(255)
upd_usrvarchar(10)
ope_timetimestamp操作日時
ope_usrvarchar(10)操作者
table_log

テーブル作成クエリはこんな感じ。PKを指定し忘れたので後付してます。

mysql> CREATE TABLE table_x (
    ->  id integer,
    ->  name varchar(30),
    ->  address varchar(255),
    ->  upd_user varchar(10)
    -> );
Query OK, 0 rows affected (0.21 sec)
mysql> ALTER TABLE table_x ADD PRIMARY KEY (id);
Query OK, 0 rows affected (0.30 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> CREATE TABLE table_log (
    ->  id integer,
    ->  name varchar(30),
    ->  address varchar(255),
    ->  upd_user varchar(10),
    ->  ope_time timestamp,
    ->  ope_user varchar(10)
    -> );
Query OK, 0 rows affected (0.06 sec)

レコード追加時に動くトリガ

table_xへのレコード追加に連動して動く処理を書きます。NEWで更新後の情報を得られます。

mysql> delimiter $$
mysql> CREATE TRIGGER trg_x_insert BEFORE INSERT ON table_x
    ->  FOR EACH ROW
    ->  BEGIN
    ->   INSERT INTO table_log (id, name, address, upd_user, ope_user)
    ->     VALUES (NEW.id, NEW.name, NEW.address, NEW.upd_user, NEW.upd_user);
    ->  END;
    -> $$
Query OK, 0 rows affected (0.08 sec)

区切り記号の変更も一緒にしています。トリガとして登録する内容の区切り記号を誤認してしまわないための措置です。

動作テスト

mysql> insert into table_x (id, name, address, upd_user) values 1,'john','tokyo','user001');
Query OK, 1 row affected (0.05 sec)
mysql> select * from table_x;
+----+------+---------+----------+
| id | name | address | upd_user |
+----+------+---------+----------+
|  1 | john | tokyo   | user001  |
+----+------+---------+----------+
1 row in set (0.00 sec)
mysql> select * from table_log;
+------+------+---------+---------------------+----------+----------+
| id   | name | address | ope_time            | ope_user | upd_user |
+------+------+---------+---------------------+----------+----------+
|    1 | john | tokyo   | 2012-04-03 15:23:03 | user001  | user001  |
+------+------+---------+---------------------+----------+----------+
1 row in set (0.00 sec)

レコード更新時に働くトリガ

trigger_eventをUPDATEとして同様に登録します。今回は使用していませんが、OLDで更新前の情報が得られます。

mysql> CREATE TRIGGER trg_x_update BEFORE UPDATE ON table_x
    ->  FOR EACH ROW
    ->  BEGIN
    ->   INSERT INTO table_log (id, name, address, upd_user, ope_user)
    ->     VALUES (NEW.id, NEW.name, NEW.address, NEW.upd_user, NEW.upd_user);
    ->  END;
    -> $$
Query OK, 0 rows affected (0.06 sec)

レコード削除時に働くトリガ

trigger_eventをDELETEとして登録します。ただし操作ユーザは取得できないため、NULLのままとしています。また、deleteですとNEW情報は使えません。OLD情報のみとなります。

mysql> CREATE TRIGGER trg_x_delete BEFORE DELETE ON table_x
    ->  FOR EACH ROW
    ->  BEGIN
    ->   INSERT INTO table_log (id, name, address, upd_user, ope_user)
    ->     VALUES (OLD.id, NULL, NULL, NULL, NULL);
    ->  END;
    -> $$
Query OK, 0 rows affected (0.06 sec)

追加→変更→削除を行うと、table_logには下記のようなデータが残ります。

mysql> select * from table_log;
+------+-------+---------+---------------------+----------+----------+
| id   | name  | address | ope_time            | ope_user | upd_user |
+------+-------+---------+---------------------+----------+----------+
|    1 | john  | tokyo   | 2012-04-03 15:23:03 | user001  | user001  |
|    1 | jimmy | tokyo   | 2012-04-03 16:11:45 | user001  | user001  |
|    1 | NULL  | NULL    | 2012-04-03 16:12:13 | NULL     | NULL     |
+------+-------+---------+---------------------+----------+----------+
3 rows in set (0.00 sec)

LEAVE A COMMENT