Zenn
コミューン株式会社Publicationへの投稿
🫠

MySQL トリガの中のクエリから別のトリガが呼ばれる場合の実行順を確認した

もろきゅう
2024/12/12に公開
MySQL
Database
リファクタリング
trigger
tech

はじめに

この記事はCommune Advent Calendar 2024、シリーズ1の9日目の記事です

きっかけ

「データベース・リファクタリング : データベースの体質改善テクニック」という書籍では、列の移動などを行う際にトリガを使ってデータの整合性を保つ方法が紹介されています
https://ndlsearch.ndl.go.jp/books/R100000002-I000009314097

例えばAテーブルからBテーブルに列を移動させる場合、移行中はトリガを使って両方のテーブルを更新するというような方法です
実現する場合、何をどう更新するのかは仕様や要件にもよるので都度検討しつつ、Aテーブルのデータを更新するためにBテーブルのトリガ、Bテーブルのデータを更新するためにAテーブルのトリガが必要になるので、処理を循環させない工夫が必要になります

やってみた(そして一旦棚上げ)

上で貼り付けた画像のようにemployeesがlicenseの属性を持っていて、それを分離させると想定してトリガを作ってみようとしたのですが「今後実装されるクエリを想定した場合、licensesだけinsertされて良いんだっけ…? ではその場合のemployeesのトリガでは…」など、様々なケースを考慮しつつ、且つその状態で処理が循環しないようにしなければならず、とても複雑です
なのでまずは、トリガの中でSQLを発行した場合どういった順序でトリガが呼ばれるのかを確認することにしました

結論

  • 元のトリガ(親)に書かれたクエリを実行して更にトリガ(子)が呼び出される場合、親トリガの終了を待たずにクエリに紐付く子トリガが先に実行されました
  • 親のbefore(update)トリガでクエリを実行した場合、子のbeforeトリガとafterトリガが呼ばれた後に親のbeforeトリガが終了しました

準備

環境

mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.31    |
+-----------+

テーブル

masters : details = 1 : n、ログ出力のためにtriggerlogテーブルを作りました

CREATE TABLE `masters` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

CREATE TABLE `details` (
  `id` int NOT NULL AUTO_INCREMENT,
  `masterId` int NOT NULL,
  `name` varchar(255) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  KEY `fk_master_detail` (`masterId`),
  CONSTRAINT `fk_master_detail` FOREIGN KEY (`masterId`) REFERENCES `masters` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

CREATE TABLE `triggerlog` (
  `id` int NOT NULL AUTO_INCREMENT,
  `message` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=209 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

トリガ

次のトリガを作り、ログ用のテーブルに適当な文字を出力します

  • 親テーブルであるmastersが更新されると、子テーブルのdetailsを更新する
  • 子テーブルのafter update, before update
DELIMITER //
DROP TRIGGER IF EXISTS master_update_after;
CREATE DEFINER=`root`@`%` TRIGGER `master_update_after` AFTER UPDATE ON `masters` FOR EACH ROW BEGIN
	INSERT INTO TriggerLog (message) VALUES ("master_update_after: start");
	update details set name="master_update_after" where id = 1;
	INSERT INTO TriggerLog (message) VALUES ("master_update_after: end");
END;
//

DROP TRIGGER IF EXISTS master_update_before;
CREATE DEFINER=`root`@`%` TRIGGER `master_update_before` BEFORE UPDATE ON `masters` FOR EACH ROW BEGIN
	INSERT INTO TriggerLog (message) VALUES ("master_update_before: start");
	update details set name="master_update_before" where id = 1;
	INSERT INTO TriggerLog (message) VALUES ("master_update_before: end");
END;
//

DROP TRIGGER IF EXISTS detail_update_after;
CREATE DEFINER=`root`@`%` TRIGGER `detail_update_after` AFTER UPDATE ON `details` FOR EACH ROW BEGIN
	INSERT INTO TriggerLog (message) VALUES ("detail_update_after");
END;
//

DROP TRIGGER IF EXISTS detail_update_before;
CREATE DEFINER=`root`@`%` TRIGGER `detail_update_before` BEFORE UPDATE ON `details` FOR EACH ROW BEGIN
	INSERT INTO TriggerLog (message) VALUES ("detail_update_before");
END;
//

DELIMITER ;

実行と結果

更新できれば良いので、適当なデータを作って更新します

insert into masters (name) values ("hogefuga");
insert into details (masterId, name) values (1, "hogefuga");

insert into triggerlog (message) value ("update start");
update masters set name="hoge" where id = 1;
insert into triggerlog (message) value ("update end");

triggerlogテーブルに残ったログ

+-----+-----------------------------+---------------------+
| id  | message                     | created_at          |
+-----+-----------------------------+---------------------+
| 199 | update start                | 2024-12-10 10:16:40 |
| 200 | master_update_before: start | 2024-12-10 10:16:40 |
| 201 | detail_update_before        | 2024-12-10 10:16:40 |
| 202 | detail_update_after         | 2024-12-10 10:16:40 |
| 203 | master_update_before: end   | 2024-12-10 10:16:40 |
| 204 | master_update_after: start  | 2024-12-10 10:16:40 |
| 205 | detail_update_before        | 2024-12-10 10:16:40 |
| 206 | detail_update_after         | 2024-12-10 10:16:40 |
| 207 | master_update_after: end    | 2024-12-10 10:16:40 |
| 208 | update end                  | 2024-12-10 10:16:40 |
+-----+-----------------------------+---------------------+

親トリガの中で別のテーブルを更新すると、別のテーブルに対応する子トリガが即時実行されることを確認しました
また、親トリガが終了する前に子のbeforeとafterトリガが実行されています
もしかすると、親トリガの処理が終わってから改めて子トリガが呼ばれたり、親子問わずbeforeがまとめて実行されてからafterの実行に移るかも…と思いましたが、結果は違いました

おまけ セッション変数について

循環トリガを防ぐ事を考えると、親トリガの中でセッション変数に適当な値をいれておいて、呼び出される子トリガではセッション変数の値を見てIF文で制御を入れる、といった方法でとりあえずは循環を防げるのではないかと思いました
なので、ついでにセッション変数の値も見ておきました

トリガ

DELIMITER //
DROP TRIGGER IF EXISTS master_update_after;
CREATE DEFINER=`root`@`%` TRIGGER `master_update_after` AFTER UPDATE ON `masters` FOR EACH ROW BEGIN
    INSERT INTO TriggerLog (message) VALUES ("master_update_after: start");
    INSERT INTO TriggerLog (message) VALUES (concat("受け取った@value : ", @value));
    SET @value = "master_update_after: start";
    INSERT INTO TriggerLog (message) VALUES (concat("渡す@value : ", @value));

    update details set name="master_update_after" where id = 1;
    INSERT INTO TriggerLog (message) VALUES ("master_update_after: end");
    INSERT INTO TriggerLog (message) VALUES (concat("受け取った@value : ", @value));
    SET @value = "master_update_after: end";
    INSERT INTO TriggerLog (message) VALUES (concat("渡す@value : ", @value));
END;
//

DROP TRIGGER IF EXISTS detail_update_after;
CREATE DEFINER=`root`@`%` TRIGGER `detail_update_after` AFTER UPDATE ON `details` FOR EACH ROW BEGIN
    INSERT INTO TriggerLog (message) VALUES ("detail_update_after");
    INSERT INTO TriggerLog (message) VALUES (concat("受け取った@value : ", @value));
    SET @value = "detail_update_after";
    INSERT INTO TriggerLog (message) VALUES (concat("渡す@value : ", @value));
END;
//

DELIMITER ;

結果

+-----+----------------------------------------------------+---------------------+
| id  | message                                            | created_at          |
+-----+----------------------------------------------------+---------------------+
| 266 | update start                                       | 2024-12-11 13:19:48 |
| 267 | @value: 初期値hogefuga                             | 2024-12-11 13:19:48 |
| 268 | master_update_after: start                         | 2024-12-11 13:19:49 |
| 269 | 受け取った@value : 初期値hogefuga                  | 2024-12-11 13:19:49 |
| 270 | 渡す@value : master_update_after: start            | 2024-12-11 13:19:49 |
| 271 | detail_update_after                                | 2024-12-11 13:19:49 |
| 272 | 受け取った@value : master_update_after: start      | 2024-12-11 13:19:49 |
| 273 | 渡す@value : detail_update_after                   | 2024-12-11 13:19:49 |
| 274 | master_update_after: end                           | 2024-12-11 13:19:49 |
| 275 | 受け取った@value : detail_update_after             | 2024-12-11 13:19:49 |
| 276 | 渡す@value : master_update_after: end              | 2024-12-11 13:19:49 |
| 277 | update end                                         | 2024-12-11 13:19:49 |
| 278 | @value: master_update_after: end                   | 2024-12-11 13:19:49 |
+-----+----------------------------------------------------+---------------------+

当たり前といえば当たり前ですが、一つの変数を呼ばれた順番に更新するだけでした

コミューン株式会社
コミューン株式会社Publication

コミューン株式会社のメンバーによる技術発信を行います。

Discussion