MySQL TriggerのNEWとOLDを使いこなす
投稿日: 2018/06/05 更新日: 2018/06/06
MySQL
Trigger(トリガー)とは
MySQLには
別のSQLとは、もう少し詳しく言うとストアドプロシージャ。
JavaやPHPなどのプログラミング言語を用いずとも、 条件分岐や繰り返しといったプログラムを書くことができます。
トリガーの構文(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 }
詳しくは 13.1.19 CREATE TRIGGER 構文を参照してください。
トリガーの使い道
トリガーは、データが変更されたときを検知して処理を実行するので、いろいろと使い道はありそうですよね。
私の考えるものは次の通りです。
- ログ
- アプリケーションロジック
- データ移行
- 集計
ちなみに、私個人的にはトリガーはおすすめしません。
※タイトルに「使いこなす」とか書いちゃってますが、正直使いたくはないです(笑)
デメリットが大きくて、システム開発には向いていないと思っているのです。
もしよければこちらの記事も参考にしてみてください。
『MySQL Triggerのデメリットが大きいので使わないで欲しい』
まずは、トリガーの使い方やNEWとOLDの説明をします。
トリガー特有のNEWとOLD
トリガーには、普通のSQLにはない NEWと OLDという構文があります。
トリガー自体、アプリケーションロジック担当者だとなかなか見ないですよね。たぶん。
まぁ、私が無知なだけな気もしますので、NEWとOLDについてまとめてみます。
NEWはトリガー実行時にこれから入れる値を扱う
NEWという単語を読んで字のごとく、INSERTやUPDATEしたときの、新しい値を扱う一時テーブル(?)のことです。
※一時テーブル?か何かはDBAが詳しく説明してくれるはず。。
NEW.カラム名
という書き方で値を参照できます。
新しい値を扱いますので、当然ながらDELETEトリガーでは使えません。
トリガー定義するときにエラーになります。
OLDはトリガー実行時に前の値を扱う
こちらもOLDという単語を読んで字のごとく、UPDATEやDELETEをしたときの、古い値を扱う一時テーブル(?)です。
OLD.カラム名
という書き方で値を参照できます。
古い値を扱いますので、当然ながらINSERTトリガーでは使えません。
こちらもトリガー定義するときにエラーになります。
NEWとOLDの意味まとめ
構文 | 意味 | INSERT | UPDATE | DELETE |
NEW | トリガー実行時の新しい値 | ○ 使える | ○ 使える | × 使えない |
OLD | トリガー実行時の古い値 | × 使えない | ○ 使える | ○ 使える |
トリガーのNEWとOLDのサンプル
簡単なサンプルでトリガーの動作を確認してみましょう。
次の流れで進めていきます。
- テーブル作成
- INSERTトリガー作成
- UPDATEトリガー作成
- DELETEトリガー作成
- データ投入してテスト
テーブル作成
まずは、二つのテーブルを作成してみます。
トリガーを設定するテーブルと、そのトリガーから操作するためのテーブルです。
用意するテーブル
- ユーザテーブル
- 履歴テーブル
ユーザテーブルにレコードが作られたりしたタイミングで、履歴テーブルにも書き込むイメージですね。
-- DROP TABLE IF EXISTS `user`;
CREATE TABLE `user` (
`user_id` int (11) NOT NULL COMMENT 'ユーザID'
, `name` varchar (4) NOT NULL COMMENT '名前'
, PRIMARY KEY (`user_id`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8 COMMENT = 'ユーザ';
-- DROP TABLE IF EXISTS `user_log`;
CREATE TABLE `user_log` (
`id` int (11) NOT NULL AUTO_INCREMENT COMMENT 'ID'
, `user_id` int (11) NOT NULL COMMENT 'ユーザID'
, `old_name` varchar (4) DEFAULT NULL COMMENT '名前(変更前)'
, `new_name` varchar (4) DEFAULT NULL COMMENT '名前(変更後)'
, PRIMARY KEY (`id`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8 COMMENT = 'ユーザ履歴';
INSERTトリガー作成
次に、INSERTされたときに実行されるトリガーを作成してみます。
ここでNEWを使ってみましょう。
OLDは使えませんので注意してください。
-- DROP TRIGGER IF EXISTS `trigger_user_log_insert`;
DELIMITER //
CREATE TRIGGER `trigger_user_log_insert` AFTER INSERT ON `user`
FOR EACH ROW
BEGIN
INSERT INTO `user_log`
SET
`user_id` = NEW.`user_id`
, `new_name` = NEW.`name`;
END;
//
DELIMITER;
UPDATEトリガー作成
UPDATEされたときに実行されるトリガーも作成してみます。
NEWとOLDの両方が使えますね。
-- DROP TRIGGER IF EXISTS `trigger_user_log_update`;
DELIMITER //
CREATE TRIGGER `trigger_user_log_update` AFTER UPDATE ON `user`
FOR EACH ROW
BEGIN
INSERT INTO `user_log`
SET
`user_id` = NEW.`user_id`
, `old_name` = OLD.`name`
, `new_name` = NEW.`name`;
END;
//
DELIMITER;
DELETEトリガー作成
DELETEされたときに実行されるトリガーも作成してみます。
NEWは使えず、OLDのみ使うことができます。
-- DROP TRIGGER IF EXISTS `trigger_user_log_delete`;
DELIMITER //
CREATE TRIGGER `trigger_user_log_delete` AFTER DELETE ON `user`
FOR EACH ROW
BEGIN
INSERT INTO `user_log`
SET
`user_id` = OLD.`user_id`
, `old_name` = OLD.`name`;
END;
//
DELIMITER;
ちなみに、業務システムではほとんどの場合でDELETEは使いません。
間違ってデータを削除してしまったときに、戻せないと困るからです。
削除フラグのようなものを用意して、論理削除するのが一般的です。
他にも、運用を続けているとログなどのデータ容量が肥大化してしまい、削除しないといけないケースも出てきます。
しかしその際はTRUNCATEやDROP PARTITIONなど高速で削除することを検討しますし、トリガーで検知する何かでもないような気がします。
仮に検知対象だとしても、性能面を考慮して、トリガーではない何かを使う気もします。
話が逸れました......。
データ投入してテスト
テーブルとトリガーを作成できたので、最後にデータを投入してテストをしてみましょう。
-- TRUNCATE `user`;
-- TRUNCATE `user_log`;
INSERT INTO `user` SET `user_id` = 1, `name` = 'one';
INSERT INTO `user` SET `user_id` = 2, `name` = 'two';
UPDATE `user` SET `name` = 'i' WHERE `user_id` = 1;
DELETE FROM `user` WHERE `user_id` = 1;
INSERTが2レコード分、UPDATEが1レコード分、DELETEが1レコード分、履歴テーブルに書き込まれる想定です。
いざ実行!
mysql> select * from user;
+---------+------+
| user_id | name |
+---------+------+
| 2 | two |
+---------+------+
1 row in set (0.00 sec)
mysql> select * from user_log;
+----+---------+----------+----------+
| id | user_id | old_name | new_name |
+----+---------+----------+----------+
| 1 | 1 | NULL | one |
| 2 | 2 | NULL | two |
| 3 | 1 | one | i |
| 4 | 1 | i | NULL |
+----+---------+----------+----------+
4 rows in set (0.00 sec)
NEWとOLDの値が、それぞれ履歴テーブルに書き込まれていることが確認できましたね。
これで、Hello Worldは完了です。