MySQLトリガー：UPDATE時にのみ実行されるしくみとサンプルコード

2024-06-30

MySQLトリガー：行更新時にのみ実行されるしくみ

MySQLトリガーは、データベース操作（INSERT、UPDATE、DELETE）発生時に自動的に実行されるプログラムです。今回取り上げるのは、UPDATE操作時にのみ実行され、かつ行の値が実際に変更された場合にのみ処理を実行するトリガーについてです。

この機能は、例えば以下のような場合に役立ちます。

更新された行の監査ログを記録する
更新内容に基づいて、別のテーブルにデータを自動的に更新する
データの整合性を保つために、制約を追加でチェックする

仕組み

MySQLトリガーは、BEFOREとAFTERの2種類に分類されます。

BEFOREトリガー: データベース操作前に実行されます。このトリガーを使用して、操作をキャンセルしたり、データを変更したりすることができます。

行更新時にのみ実行されるトリガーを作成するには、AFTER UPDATEトリガーを使用します。さらに、WHERE NEW.カラム名 <> OLD.カラム名という条件を追加することで、行の値が実際に変更された場合にのみ処理を実行することができます。

例

以下の例では、mytableテーブルのupdated_atカラムが更新された場合にのみ、audit_logテーブルにログを記録するトリガーを作成します。

CREATE TRIGGER audit_log AFTER UPDATE ON mytable
FOR EACH ROW
BEGIN
  IF NEW.updated_at <> OLD.updated_at THEN
    INSERT INTO audit_log (table_name, row_id, updated_column, old_value, new_value)
    VALUES ('mytable', NEW.id, 'updated_at', OLD.updated_at, NEW.updated_at);
  END IF;
END;

補足

トリガーは、データベース操作のパフォーマンスに影響を与える可能性があることに注意する必要があります。
複雑な処理を実行する場合は、トリガーではなく、ストアドプロシージャを使用することを検討してください。
トリガーを作成する前に、その動作を十分にテストしてください。

サンプルコード：顧客テーブルの住所更新時にログを記録するトリガー

DELIMITER $$

CREATE TRIGGER address_update_log
AFTER UPDATE ON customers
FOR EACH ROW
BEGIN
  IF NEW.address <> OLD.address THEN
    INSERT INTO address_update_log (customer_id, old_address, new_address)
    VALUES (NEW.id, OLD.address, NEW.address);
  END IF;
END $$

DELIMITER ;

解説

DELIMITER $$: トリガー定義の区切り文字を $$ に変更します。これは、トリガー定義内でセミコロン (;) を使用するために行います。
CREATE TRIGGER address_update_log: トリガーの名前を address_update_log に設定します。
AFTER UPDATE ON customers: このトリガーは、customers テーブルに対して UPDATE 操作が実行された後に実行されます。
FOR EACH ROW: トリガーは、更新されたテーブルの各行に対して実行されます。
BEGIN: トリガーの処理ブロックの開始を示します。
IF NEW.address <> OLD.address: address カラムの値が更新前と更新後で異なる場合のみ、以下の処理を実行します。
INSERT INTO address_update_log ...: address_update_log テーブルに新しいレコードを挿入します。このレコードには、顧客ID (customer_id)、更新前の住所 (old_address)、更新後の住所 (new_address) が格納されます。
END IF;: IF ステートメントの終了を示します。
$$: トリガー定義の区切り文字を元に戻します。

注意事項

このトリガーは、customers テーブルの address カラムが更新されたすべての行に対して実行されます。特定の行のみを対象にしたい場合は、WHERE 条件を追加する必要があります。

このサンプルコードを参考に、ご自身のニーズに合わせてトリガーをカスタマイズしていただければと思います。

MySQLトリガー以外の代替方法

ストアドプロシージャは、データベース内で定義される再利用可能なプログラムです。トリガーと同様に、データベース操作の発生時に自動的に実行することができます。

利点

トリガーよりも柔軟性と制御性に優れています。
複雑な処理をトリガーよりも効率的に実行することができます。
複数のトリガーを1つのストアドプロシージャにまとめることができます。

欠点

トリガーよりも作成とメンテナンスが複雑です。
トリガーよりもパフォーマンスが劣る場合があります。

以下のストアドプロシージャは、customers テーブルの住所 (address) が更新された際に、更新内容をログテーブル (address_update_log) に記録します。

DELIMITER $$

CREATE PROCEDURE log_address_update(customer_id INT, old_address VARCHAR(255), new_address VARCHAR(255))
BEGIN
  INSERT INTO address_update_log (customer_id, old_address, new_address)
  VALUES (customer_id, old_address, new_address);
END $$

DELIMITER ;

CREATE TRIGGER address_update_log
AFTER UPDATE ON customers
FOR EACH ROW
BEGIN
  CALL log_address_update(NEW.id, OLD.address, NEW.address);
END;

UPDATEステートメントとサブクエリを使用して、更新対象の行のみを抽出することができます。その後、抽出された行に対して、必要な処理を実行することができます。

トリガーやストアドプロシージャを使用するよりもシンプルです。
複雑なロジックを記述する必要がない場合があります。

複数の行を更新する場合は、複雑になる可能性があります。

UPDATE customers c
JOIN address_update_log l ON c.id = l.customer_id
SET l.old_address = c.address,
    l.new_address = NEW.address
WHERE c.address <> NEW.address;

変更データキャプチャ (CDC)

MySQLは、変更データキャプチャ (CDC) と呼ばれる機能を提供しています。CDCを使用すると、データベースに対する変更をリアルタイムで追跡することができます。