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

この記事は[スタンバイ Advent Calendar 2022](https://qiita.com/advent-calendar/2022/stanby)の23日目の記事です。

最近、MySQLデータの更新にtrigger機能を検証したので、一般的にはNGとされている機能が実現できたことを記載します

# 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で出来ないこと
- [ドキュメント](https://dev.mysql.com/doc/refman/8.0/ja/stored-program-restrictions.html)によると、trigger内で同一テーブルの更新を行うことが出来ない。
>ストアドファンクションまたはトリガーは、そのストアドファンクションまたはトリガーを呼び出したステートメントによって (読み取りまたは書き込みに) すでに使用されているテーブルを変更できません。
- 正しくは、「同一テーブルに更新クエリを発行することが出来ない」であり、本稿ではtrigger内で同一テーブルのデータを更新する方法を記載する

## MySQL Triggerの作成方法
本記事のメインでもある「trigger内で同一テーブルの更新を行う」ことが実現できるテーブル構成で例示をします。
Trigger構文の詳細は、[[create trigger構文](https://dev.mysql.com/doc/refman/8.0/ja/create-trigger.html)]を参照してください。

### 構成テーブル
|テーブル名|役割｜
|:------------:|:------------:|
|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更新クエリの内容を、実行直前に置き換えられるのはかなり驚きである
* データ修正の仕組みを低レイヤーで簡単に実装出来るので、かなり有用な方法である
* ログ等の仕組みが出しにくいので、アプリケーション処理との融合は難しい
* 結局、使い所次第ではある