MySQLのトリガーを使用して特定のテーブルの操作を行った際に別テーブルにログを残す方法

MySQLにトリガーという機能があるらしくどのようなものか調べたところ、事前に「指定したテーブルでINSERT / UPDATE / DELETEが行われたら〇〇というSQL文を実行する」というものらしく、管理画面等の操作ログなんかに使えそう。以下に導入方法をメモ。

リファレンス

https://dev.mysql.com/doc/refman/8.0/ja/triggers.html

やりたいこと

test_tableにデータがINSERTされたらlog_tableに「INSERT」と「実行日時」を保存したい。各テーブルは以下のような設計。

mysql> SHOW COLUMNS FROM `test_table`;
+------------+--------------+------+-----+---------+----------------+
| Field      | Type         | Null | Key | Default | Extra          |
+------------+--------------+------+-----+---------+----------------+
| id         | int          | NO   | PRI | NULL    | auto_increment |
| name       | varchar(255) | NO   |     | NULL    |                |
| score      | int          | NO   |     | NULL    |                |
| regist_ymd | datetime     | NO   |     | NULL    |                |
+------------+--------------+------+-----+---------+----------------+

mysql> SHOW COLUMNS FROM `log_table`;
+------------+--------------+------+-----+---------+----------------+
| Field      | Type         | Null | Key | Default | Extra          |
+------------+--------------+------+-----+---------+----------------+
| id         | int          | NO   | PRI | NULL    | auto_increment |
| log        | varchar(255) | NO   |     | NULL    |                |
| regist_ymd | datetime     | NO   |     | NULL    |                |
+------------+--------------+------+-----+---------+----------------+

トリガーについて

基本構文

CREATE TRIGGER 【トリガー名】
{ BEFORE | AFTER } { INSERT | UPDATE | DELETE }
ON 【トリガーが発火するテーブル】 FOR EACH ROW
【発火内容】;

トリガーの追加

CREATE TRIGGER insert_trigget 
AFTER INSERT ON `test_table` 
FOR EACH ROW 
INSERT INTO `log_table` 
    (
        `log`,
        `regist_ymd`
    )
VALUES 
    (
        'INSERT',
        now()
    );

今回はINSERTのみ設定したがUPDATE / DELETEもそれぞれ別で登録可。

トリガーの確認

SHOW TRIGGERS;

トリガーの実行

INSERT INTO `test_table` 
    (`name`, `score`, `regist_ymd`) 
VALUES 
    ("鈴木", 80, now());

mysql> SELECT * FROM `test_table`;
+----+--------+-------+---------------------+
| id | name | score | regist_ymd |
+----+--------+-------+---------------------+
| 1 | 鈴木 | 80 | 2024-02-15 18:13:49 |
+----+--------+-------+---------------------+

mysql> SELECT * FROM `log_table`;
+----+--------+---------------------+
| id | log | regist_ymd |
+----+--------+---------------------+
| 1 | INSERT | 2024-02-15 18:13:49 |
+----+--------+---------------------+

test_tableにINSERTすることでlog_tableへもデータが登録された点が確認できる。

トリガーの削除

DROP TRIGGER insert_trigget;

その他

Adminerを利用している場合以下から設定できるみたい。

時間(BEFORE / AFTER)やイベント(INSERT / UPDATE / DELETE)をGUIで選択でき、テキストエリアには以下の発火内容を入力すると登録できる。

INSERT INTO `log_table` 
    (
        `log`,
        `regist_ymd`
    )
VALUES 
    (
        'INSERT',
        now()
    );