備忘録‎ > ‎

[mysql]トリガ作成例

トリガの書式

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

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_timetrigger_eventで複数の処理を登録することは出来ない。

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

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

準備

説明用テーブル構成、アドレス帳っぽいものを想定。
  • table_x
     id  integer PK指定
     name  varchar(30) 
     address  varchar(255) 
     upd_user varchar(10)  
ログ用テーブルはほとんど同じ構成で操作日時や操作者を記録できるようにしておく。また、PK指定は外しておく。
  • table_log
     id  integer  PK指定しない
     name  varchar(30)  
     address  varchar(255) 
     upd_user  varchar(10) 
     ope_time  timestamp 操作日時
     ope_user varchar(10) 操作者
テーブル作成クエリはこんな感じ。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)

 
 
Comments