トリガーとは？
SQLのトリガーとは、テーブルのデータが更新された場合など何かのイベントをきっかけにして別の処理を行う仕組みのことです。
具体的には、INSERT INTO文やUPDATE文、DELETE文などのデータを操作するSQL文が当てはまります。
それぞれのトリガーの種類は次の通りです。
- INSERTトリガー：テーブルにデータが追加される前後に、設定したSQL文が実行されます。
- UPDATEトリガー：テーブルのデータが更新される前後に、設定したSQL文が実行されます。
- DELETEトリガー：テーブルのデータが削除される前後に、設定したSQL文が実行されます。ただし、DROP TABLE文 でテーブルが削除された場合は起動しません。
また、トリガーを定義する場合は、次のような指定を加えましょう。
- 対象のテーブル
- トリガーを起動する処理
- トリガーの処理内容
- トリガーの起動タイミング
上記の指定をすることで、トリガーはテーブルの更新状況を監視し、しかるべきタイミングでトリガー処理を実行します。
トリガーの作成方法・使い方
トリガーはどのように作成するのですか？
ここでは、MySQLを使ってトリガーを作成する方法を見ていきましょう！
MySQLの場合は、次のようにトリガーを作成します。
CREATE
[DEFINER = { user | CURRENT_USER }]
TRIGGER trigger_name
trigger_time trigger_event
ON tbl_name FOR EACH ROW
trigger_body
trigger_time: { BEFORE | AFTER }
trigger_event: { INSERT | UPDATE | DELETE }
それでは実際にトリガーを作成して試してみましょう。
まず、トリガーの対象になるテーブルを次のように作成しました。
CREATE TABLE user_list(user_id int auto_increment primary key, name varchar(10), age int);
また、トリガーが起動したときにログを記録する用のテーブル作成します。
CREATE TABLE user_list_log(log_id int auto_increment primary key, log varchar(20), time datetime);
INSERTトリガーを作成
ではまず、INSERTトリガーを作成してみましょう。次のように記述します。
CREATE TRIGGER insert_trigger AFTER INSERT ON user_list FOR EACH ROW INSERT INTO user_list_log (log, time) VALUES('Insert', now());
上記のトリガーは、user_listテーブルにデータを挿入し後に user_list_logテーブルにログを書き込みます。
ではトリガー対象の user_listテーブルに、3つデータを挿入してみましょう。
INSERT INTO user_list VALUES(1, 'Tanaka', 23); INSERT INTO user_list VALUES(2, 'Okada', 35); INSERT INTO user_list VALUES(3, 'Watanabe', 28);
user_listテーブルにデータを3つ挿入したので、user_list_logテーブルにも3つのデータが追加されているはずです。
SELECT文でuser_list_logテーブルの中身を確認してみます。
mysql> SELECT * FROM user_list_log; +--------+--------+---------------------+ | log_id | log | time | +--------+--------+---------------------+ | 1 | Insert | 2020-09-30 15:33:18 | | 2 | Insert | 2020-09-30 15:33:51 | | 3 | Insert | 2020-09-30 15:33:55 | +--------+--------+---------------------+ 3 rows in set (0.00 sec)
ちゃんとトリガーが起動し、ログテーブルにもデータが追加されました。
UPDATEトリガーを作成
次に、データをアップデートした時に起動するUPDATEトリガーを作成します。
INSERTトリガーを作成してみましょう。次のように記述します。
CREATE TRIGGER update_trigger AFTER UPDATE ON user_list FOR EACH ROW INSERT INTO user_list_log (log, time) VALUES('Update', now());
UPDATEトリガーを作成したら、対象テーブルのデータを更新してみましょう。
UPDATE user_list SET name = 'Yamada' WHERE user_id=3;
先ほど同様にuser_list_logテーブルの中身を確認してみます。
mysql> SELECT * FROM user_list_log; +--------+--------+---------------------+ | log_id | log | time | +--------+--------+---------------------+ | 1 | Insert | 2020-09-30 15:33:18 | | 2 | Insert | 2020-09-30 15:33:51 | | 3 | Insert | 2020-09-30 15:33:55 | | 4 | Update | 2020-09-30 16:09:29 | +--------+--------+---------------------+ 4 rows in set (0.01 sec)
UPDATEトリガーが起動し、user_list_logテーブルに書き込まれているのがわかります。
DELETEトリガーを作成
最後に、DELETEトリガーも作成してみましょう。
次のように記述して作成します。
CREATE TRIGGER delete_trigger AFTER DELETE ON user_list FOR EACH ROW INSERT INTO user_list_log (log, time) VALUES('Delete', now());
DELETEトリガーを作成したら、対象テーブルのデータを何か1つ削除してみましょう。
DELETE FROM user_list WHERE user_id = 1;
トリガーのログも確認してみます。
mysql> SELECT * FROM user_list_log; +--------+--------+---------------------+ | log_id | log | time | +--------+--------+---------------------+ | 1 | Insert | 2020-09-30 15:33:18 | | 2 | Insert | 2020-09-30 15:33:51 | | 3 | Insert | 2020-09-30 15:33:55 | | 4 | Update | 2020-09-30 16:09:29 | | 5 | Delete | 2020-09-30 16:14:26 | +--------+--------+---------------------+ 5 rows in set (0.00 sec)
最終行にDeleteのログが追加されています！
作成したトリガーの確認方法
MySQLで作成したトリガーを一覧で確認するには「SHOW TRIGGERS文」を使いましょう。
SQLは次のように記述します。
SHOW TRIGGERS [FROM db_name]
[LIKE 'pattern' | WHERE expr]
SHOW TRIGGERS の後に、トリガーを作成しているデータベース名をしてしましょう。
また、LIKE句や WHERE句を使用して確認するトリガーを絞り込むことも可能です。
では実際に、先ほど作成したトリガーを確認してみます。
mysql> SHOW TRIGGERS mydb;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'mydb' at line 1
mysql> SHOW TRIGGERS FROM mydb;
+----------------+--------+-----------+---------------------------------------------------------------+--------+------------------------+-----------------------------------------------------------------------------------------------------------------------+----------------+----------------------+----------------------+--------------------+
| Trigger | Event | Table | Statement | Timing | Created | sql_mode | Definer | character_set_client | collation_connection | Database Collation |
+----------------+--------+-----------+---------------------------------------------------------------+--------+------------------------+-----------------------------------------------------------------------------------------------------------------------+----------------+----------------------+----------------------+--------------------+
| insert_trigger | INSERT | user_list | INSERT INTO user_list_log (log, time) VALUES('Insert', now()) | AFTER | 2020-09-30 15:31:45.15 | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION | root@localhost | utf8mb4 | utf8mb4_0900_ai_ci | utf8mb4_0900_ai_ci |
| update_trigger | UPDATE | user_list | INSERT INTO user_list_log (log, time) VALUES('Update', now()) | AFTER | 2020-09-30 16:06:31.32 | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION | root@localhost | utf8mb4 | utf8mb4_0900_ai_ci | utf8mb4_0900_ai_ci |
| delete_trigger | DELETE | user_list | INSERT INTO user_list_log (log, time) VALUES('Delete', now()) | AFTER | 2020-09-30 16:14:21.95 | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION | root@localhost | utf8mb4 | utf8mb4_0900_ai_ci | utf8mb4_0900_ai_ci |
+----------------+--------+-----------+---------------------------------------------------------------+--------+------------------------+-----------------------------------------------------------------------------------------------------------------------+----------------+----------------------+----------------------+--------------------+
3 rows in set (0.03 sec)
上記のように、トリガーの情報が確認できました。
作成したトリガーの削除方法
作成したトリガーを削除するには「DROP TRIGGER文」を使いましょう。
次のように記述します。
DROP TRIGGER トリガー名;
では先ほど作成したINSERTトリガーを削除してみます。
mysql> DROP TRIGGER insert_trigger; Query OK, 0 rows affected (0.02 sec)
これで削除は完了です。
トリガーを確認してみると、削除されていることがわかるはずです。
