MySQLのトリガーについて

はじめに

昔、業務で使ったことがあるのを紹介したいと思います

業務移管で引き継いだサービスがあり、その中で利用されていました
自分はトリガーなどの存在をしらなかったため、ドキュメントもなく、アプリケーション側からはまったく追うことができないため、勝手にデータが更新されている状態で不思議でした

そのとき使われていたトリガーについて話したいと思います

トリガーの説明

MySQLにおける トリガー とは、データベース内で特定のイベント（INSERT、UPDATE、DELETEなど）が発生した際に、自動的に実行されるよう定義されたストアドプログラムの一種です
これにより、アプリケーション側での追加処理を記述することなく、データベース層で直接、データの変更を監視し、関連する操作を自動化できます

トリガーは、以下の点で役に立ちます

• データの整合性維持
  • データの追加や変更時に、関連するテーブルのデータを自動的に更新したり、制約を満たしているかチェックしたりできます
• 監査ログの記録
  • 誰が、いつ、どのようなデータを変更したかといった情報を自動的に記録できます
• 複雑なビジネスロジックの実装
  • 特定の条件に基づいて、複数のテーブルにわたる複雑な処理を自動的に実行できます

ユーザーテーブルとログテーブルを使った例で説明

ユーザーの作成や更新、削除といったイベントを記録する 監査ログ の例を通じて、トリガーの具体的な使い方を説明したいと思います

以下の2つのテーブルを作成します

• usersテーブル: ユーザー情報を格納します
• user_logsテーブル: usersテーブルへの操作履歴を記録します

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

CREATE TABLE user_logs (
    log_id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT,
    operation_type VARCHAR(50) NOT NULL,
    old_data JSON,
    new_data JSON,
    operated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

次に、usersテーブルへの操作（INSERT, UPDATE, DELETE）が発生した際に、user_logsテーブルにログを記録するトリガーを作成します

1. AFTER INSERTトリガーの例

ユーザーが新しく作成されたときにログを記録します

DELIMITER //

CREATE TRIGGER after_user_insert
AFTER INSERT ON users
FOR EACH ROW
BEGIN
    INSERT INTO user_logs (user_id, operation_type, new_data)
    VALUES (NEW.id, 'INSERT', JSON_OBJECT('id', NEW.id, 'name', NEW.name, 'email', NEW.email));
END //

DELIMITER ;

• DELIMITER // ... // DELIMITER ;
  • トリガー定義の区切り文字を一時的に変更しています
  • トリガーの内部でセミコロン;を使用するためです
• AFTER INSERT ON users
  • usersテーブルへのINSERT操作の後にトリガーが実行されます
• FOR EACH ROW
  • 挿入される行ごとにトリガーが実行されます
• NEW.id, NEW.name, NEW.email
  • 挿入される新しい行の各カラムの値を参照します

2. AFTER UPDATEトリガーの例

ユーザー情報が更新されたときにログを記録します

DELIMITER //

CREATE TRIGGER after_user_update
AFTER UPDATE ON users
FOR EACH ROW
BEGIN
    INSERT INTO user_logs (user_id, operation_type, old_data, new_data)
    VALUES (NEW.id, 'UPDATE',
            JSON_OBJECT('id', OLD.id, 'name', OLD.name, 'email', OLD.email),
            JSON_OBJECT('id', NEW.id, 'name', NEW.name, 'email', NEW.email));
END //

DELIMITER ;

• AFTER UPDATE ON users
  • usersテーブルへのUPDATE操作の後にトリガーが実行されます
• OLD.id, OLD.name, OLD.email
  • 更新される前の古い行の各カラムの値を参照します
• NEW.id, NEW.name, NEW.email
  • 更新された後の新しい行の各カラムの値を参照します

3. AFTER DELETEトリガーの例

ユーザーが削除されたときにログを記録します

DELIMITER //

CREATE TRIGGER after_user_delete
AFTER DELETE ON users
FOR EACH ROW
BEGIN
    INSERT INTO user_logs (user_id, operation_type, old_data)
    VALUES (OLD.id, 'DELETE', JSON_OBJECT('id', OLD.id, 'name', OLD.name, 'email', OLD.email));
END //

DELIMITER ;

• AFTER DELETE ON users
  • usersテーブルへのDELETE操作の後にトリガーが実行されます
  • OLD.id, OLD.name, OLD.email: 削除される行の各カラムの値を参照します

実際に試してみます

ここで試してみて動作を確認しました

ユーザーの挿入

INSERT INTO users (name, email) VALUES ('Alice', '[e-mail]');

この実行後、user_logsテーブルにはoperation_typeがINSERTのログが追加されています

ユーザーの更新

UPDATE users SET email = '[e-mail]' WHERE name = 'Alice';

この実行後、user_logsテーブルにはoperation_typeがUPDATEのログが追加され、old_dataとnew_dataで変更前後のデータが確認できます

ユーザーの削除

DELETE FROM users WHERE name = 'Alice';

この実行後、user_logsテーブルにはoperation_typeがDELETEのログが追加され、old_dataで削除されたデータが確認できます

さいごに

簡単にユーザーテーブルとログテーブルで説明しましたが、自分のときもこんな感じでした

トリガーは、データの整合性を取るのに非常に便利だと思います
アプリケーションコードが入る余地のない状態にもできるので、データベース側で堅牢なビジネスロジックを実装できます
特定のテーブルに対してのレコード操作すると自動でおこなわれているので、自分が意図しない動作をする可能性もあり、テストを十分行う必要があります

またドキュメントやコード内にコメントとか、なんらか残る形にして、データベース側にもビジネスロジックがあること伝えないと、データベース移行などのときに漏れたりして大変なことになります

今の業務では、ビジネスロジックをアプリケーション側にまとめていて、つかわなくなりましたが、昔つかったなぁ～と思って書きました