TL;DR

MySQLのtriggerで自動でデータ更新を行うことができる
公式にはできないとされている、trigger内で同一テーブルデータ更新を実施できて、安全であることが理解できる

MySQL triggerとは

Tableに対しての行操作（INSERT/UPDATE/DELETE）が行われた際に実行する操作を定義できる
triggerの起点となる処理(trigger event)の、前・後のどちらかで処理を行うことが出来る
trigger内では、SQL、procedure, functionを実行することが出来る
更新前後のカラムデータを取り扱うことができ、OLD.[カラム名]（更新前データ）、NEW.[カラム名]（更新後データ）として表現をする
一部制御構文（IF、CASE、FOR等）、DBイベントを操作することが出来る
https://dev.mysql.com/doc/refman/8.0/ja/triggers.html

triggerの使用目的は、テーブル内、テーブル間のデータ整合性を担保するために用いられるものです。処理ロジックがアプリケーションに残らないので嫌がる人も多いですが、データ観点で整合性を担保することを目的とする場合は良い手段であります。

MｙSQL triggerで出来ないこと

ドキュメントによると、trigger内で同一テーブルの更新を行うことが出来ない。

ストアドファンクションまたはトリガーは、そのストアドファンクションまたはトリガーを呼び出したステートメントによって (読み取りまたは書き込みに) すでに使用されているテーブルを変更できません。

正しくは、「同一テーブルに更新クエリを発行することが出来ない」であり、本稿ではtrigger内で同一テーブルのデータを更新する方法を記載する

MySQL Triggerの作成方法

本記事のメインでもある「trigger内で同一テーブルの更新を行う」ことが実現できるテーブル構成で例示をします。
Trigger構文の詳細は、[create trigger構文]を参照してください。

構成テーブル

テーブル名	役割｜
master_star_name	正しい星の名前が管理されているテーブル
start_neme	星の名前を入力するテーブル
star_modify_history	入力値が間違っている場合に修正した履歴

/* 星座マスタ表 */
CREATE TABLE `master_star_name` (
  `seiza` varchar(64) NOT NULL,
  `rank`  varchar(64) NOT NULL,
  `star_name` varchar(64) NOT NULL,
  PRIMARY KEY (`seiza`,`rank`))
ENGINE = InnoDB;

insert into master_star_name values ('オリオン座','1','ベテルギウス');
insert into master_star_name values ('オリオン座','2','リゲル');
insert into master_star_name values ('おおいぬ座','1','シリウス');
insert into master_star_name values ('こいぬ座','1','プロキオン');

/* 星座入力表　*/
CREATE TABLE `star_name` (
  `seiza` varchar(64) NOT NULL,
  `rank`  varchar(64) NOT NULL,
  `star_name` varchar(64) NOT NULL,
  PRIMARY KEY (`seiza`,`rank`))
ENGINE = InnoDB;

/* 星座名修正履歴表 */
CREATE TABLE `star_modify_history` (
  `seiza` varchar(64) NOT NULL,
  `rank`  varchar(64) NOT NULL,
  `original_star_name` varchar(64) NOT NULL,
  `fixed_star_name` varchar(64) NOT NULL,
  `created_at` datetime(3) NOT NULL)
  PRIMARY KEY (`seiza`,`rank`,`created_at`))
ENGINE = InnoDB;

作成trigger

DELIMITER $$
DROP TRIGGER IF EXISTS `tr_insert_star_name`$$
CREATE TRIGGER `tr_insert_star_name` BEFORE INSERT ON `star_name`  FOR EACH ROW
BEGIN

  SELECT star_name INTO @star_name 
  FROM master_star_name
  WHERE seiza = NEW.seiza AND rank = NEW.rank;

  IF NEW.star_name != @star_name THEN
    -- 履歴表（他のテーブルに処理をする）
    INSERT INTO star_modify_history VALUES (NEW.seiza, NEW.rank, NEW.star_name, @star_name, now());

    -- 入力するstar_nameを変更する（execute前なので、値を変更できる！！）
    SET NEW.star_name = @star_name;
  END IF;

END$$
DELIMITER ;

triggerの概要は「行処理実行前に、更新データを書き換える」
BEFOREの指定が重要。INSERT処理実行前に処理を行うための宣言（一般的にはAFTERを使用するが同一テーブル処理では必ずBEFORE）
SELECT star_name INTO @star_name master表から正しいstar_nameを取得して@star_nameに保管
SET NEW.star_name = @star_name これが今回の肝。更新後のデータNEW.star_nameを書き換えている
trigger処理はこのまま完了する。直後実行される行処理で書き換わったNEW.star_nameが適用される
必要な処理だけが行われるように、適時IF分で制御を入れる。今回はstar_nameが正しければ処理しない

実行してみる

間違ったデータを入力する

mysql> insert into star_name values ('オリオン座','1','リゲル');
Query OK, 1 row affected (0.01 sec)

結果確認

mysql> select * from star_name;
+-----------------+------+--------------------+
| seiza           | rank | star_name          |
+-----------------+------+--------------------+
| オリオン座         | 1    | ベテルギウス         |
+-----------------+------+--------------------+
1 row in set (0.01 sec)

mysql> select * from star_modify_history;
+-----------------+------+--------------------+--------------------+-------------------------+
| seiza           | rank | original_star_name | fixed_star_name    | created_at              |
+-----------------+------+--------------------+--------------------+-------------------------+
| オリオン座         | 1    | リゲル              | ベテルギウス          | 2022-12-17 01:21:45.000 |
+-----------------+------+--------------------+--------------------+-------------------------+
1 row in set (0.01 sec)

間違った情報を入力しても、マスタにある正しいデータに置き換わっており、履歴表にも登録されている

その他事項

トランザクションを使用した場合は、行処理、trigger処理がまとめて１トランザクションの扱いになる。rollbackするとtrigger処理も合わせて戻すことが出来る
逆にtrigger処理がエラーになると、行処理も合わせてエラー扱いになるので注意が必要

感想

DB更新クエリの内容を、実行直前に置き換えられるのはかなり驚きである
データ修正の仕組みを低レイヤーで簡単に実装出来るので、かなり有用な方法である
ログ等の仕組みが出しにくいので、アプリケーション処理との融合は難しい
結局、使い所次第ではある

MySQL Triggerで同一テーブルのデータを更新してみた