MySQLトリガー | 複数のイベント処理 | 詳細解説とサンプルコード

2024-04-28

MySQL トリガーで複数のイベントを使用する方法

MySQL、MariaDBなどのデータベースでは、トリガーを使用して、特定の操作が発生したときに自動的にアクションを実行することができます。トリガーは、INSERT、UPDATE、DELETEなどの操作をトリガーとして実行できます。また、BEFORE、AFTER、INSTEAD OFなどのタイミングを指定して、アクションを実行するタイミングを制御することもできます。

興味深いことに、ひとつのトリガーで複数のイベントを処理することが可能です。これは、複数の操作に対して共通の処理を実行したい場合や、操作の順序に依存した処理を実行したい場合などに役立ちます。

複数のイベントを処理するトリガーの構文

CREATE TRIGGER trigger_name
FOR EACH ROW
{
  BEFORE | AFTER | INSTEAD OF}
  {INSERT | UPDATE | DELETE | {INSERT | UPDATE | DELETE} ON table_name
  FOR EACH ROW
  BEGIN
    -- 処理内容
  END;

この構文で、BEFORE、AFTER、INSTEAD OF のいずれかを指定して、トリガーの実行タイミングを決定します。また、INSERT、UPDATE、DELETE のいずれかを指定して、トリガーをトリガーする操作を指定します。さらに、ON table_name を使用して、トリガーを適用するテーブルを指定します。

FOR EACH ROW は、トリガーがテーブル内の各行に対して個別に実行されることを示します。省略することもできますが、省略すると、トリガーはテーブル全体に対して一度だけ実行されます。

BEGIN と END で囲まれたブロック内に、トリガーの実行時に実行される処理を記述します。このブロック内では、SQL ステートメント、ストアドプロシージャ、ストアドファンクションなどを呼び出すことができます。

複数のイベントの処理例

次に、INSERT と UPDATE の両方の操作をトリガーとして、監査ログを記録するトリガーの例を示します。

CREATE TRIGGER audit_log
FOR EACH ROW
AFTER INSERT, UPDATE ON customers
BEGIN
  INSERT INTO audit_logs (customer_id, operation, timestamp)
  VALUES (NEW.customer_id, IFNULL(NEW.old_value, 'INSERT'), NOW());
END;

このトリガーは、customers テーブルに対して INSERT または UPDATE 操作が行われるたびに実行されます。トリガーが実行されると、audit_logs テーブルに新しい行が挿入され、挿入または更新された顧客 ID、操作の種類、およびタイムスタンプが記録されます。

NEW キーワードは、挿入または更新された行の新しい値を参照するために使用されます。OLD キーワードは、更新前の行の値を参照するために使用されます。

注意事項

複数のイベントを処理するトリガーを使用する場合は、トリガーの実行順序を考慮する必要があります。デフォルトでは、トリガーは作成された順序で実行されます。トリガーの実行順序を変更するには、FOLLOWS または PRECEDES キーワードを使用します。
複数のイベントを処理するトリガーは、複雑になりやすいため、注意して設計する必要があります。トリガーが複雑になりすぎると、パフォーマンスの問題が発生したり、予期しない動作が発生したりする可能性があります。

MySQL トリガーを使用して、複数のイベントを処理することで、データベース操作の自動化と監査を強化することができます。ただし、複数のイベントを処理するトリガーを使用する場合は、トリガーの実行順序と複雑さを考慮する必要があります。

この例では、顧客注文管理システムにおけるトリガーの使用方法を示します。このシステムでは、以下の3つのテーブルを使用します。

customers テーブル：顧客情報
orders テーブル：注文情報

トリガー

注文作成時に顧客ポイントを付与するトリガー

CREATE TRIGGER add_points_on_order_create
AFTER INSERT ON orders
FOR EACH ROW
BEGIN
  DECLARE customer_id INT;
  DECLARE points INT;

  -- 注文作成時の顧客 ID を取得
  SET customer_id = NEW.customer_id;

  -- 顧客の現在のポイント数を取得
  SELECT points INTO points FROM customers WHERE customer_id = customer_id;

  -- 注文金額に応じてポイントを付与
  UPDATE customers
  SET points = points + NEW.order_amount * 0.1
  WHERE customer_id = customer_id;
END;

このトリガーは、orders テーブルに新しい行が挿入されるたびに実行されます。トリガーが実行されると、以下の処理が行われます。

注文作成時の顧客 ID を取得します。
顧客の現在のポイント数を取得します。
注文金額に応じてポイントを付与します。

ポイント付与の計算式は、order_amount * 0.1 となっています。これは、注文金額の10%をポイントとして付与するという意味です。

注文ステータス変更時に在庫数を更新するトリガー

CREATE TRIGGER update_stock_on_order_status_change
AFTER UPDATE ON orders
FOR EACH ROW
BEGIN
  DECLARE order_id INT;
  DECLARE product_id INT;
  DECLARE quantity INT;

  -- 注文 ID を取得
  SET order_id = NEW.order_id;

  -- 注文ステータスが "出荷済み" または "完了" に変更された場合のみ処理を実行
  IF NEW.order_status IN ('shipped', 'completed') THEN
    -- 注文詳細情報から商品 ID と数量を取得
    SELECT product_id, quantity
    FROM order_details
    WHERE order_id = order_id;

    -- 在庫数を更新
    UPDATE products
    SET stock = stock - quantity
    WHERE product_id = product_id;
  END IF;
END;

注文ステータスが "出荷済み" または "完了" に変更された場合のみ処理を実行します。
注文詳細情報から商品 ID と数量を取得します。
在庫数を更新します。

在庫数の更新処理では、stock カラムから quantity を減算しています。これは、注文された商品が発送されたことを意味します。

注文削除時に注文詳細情報を削除するトリガー

CREATE TRIGGER delete_order_details_on_order_delete
BEFORE DELETE ON orders
FOR EACH ROW
BEGIN
  DECLARE order_id INT;

  -- 注文 ID を取得
  SET order_id = OLD.order_id;

  -- 注文詳細情報を削除
  DELETE FROM order_details
  WHERE order_id = order_id;
END;

注文詳細情報を削除します。

このトリガーは、orders テーブルから注文が削除されたときに、対応する order_details テーブルのエントリも自動的に削除するために使用されます。

この例は、顧客注文管理システムにおけるトリガーの使用方法を示すほんの一例です。トリガーは、データベース操作の自動化、データ整合性の維持、監査追跡など、さまざまな目的に使用することができます。

トリガー以外の代替方法

トリガー以外にも、データベース操作を自動化したり、データ整合性を維持したり、監査追跡を行ったりするための方法はいくつかあります。以下に、いくつかの代替方法と、それぞれの長所と短所を説明します。

ストアドプロシージャは、データベース内で実行される一連の SQL ステートメントをカプセル化したモジュールです。トリガーと同様に、ストアドプロシージャを使用して、特定の操作が発生したときに自動的にアクションを実行することができます。

長所:

トリガーよりも複雑なロジックを処理することができます。
再利用可能なコードを作成することができます。
複数のデータベース操作をひとつのユニットにまとめることができます。

トリガーよりも実行速度が遅い場合があります。
デバッグが難しい場合があります。

イベント駆動型アーキテクチャは、イベントが発生したときにアクションをトリガーするソフトウェア設計パターンです。このパターンでは、トリガーやストアドプロシージャの代わりに、メッセージキューなどのイベント配信システムを使用します。

スケーラブルで柔軟性の高いシステムを構築することができます。
異なるコンポーネント間で疎結合を実現することができます。
マイクロサービスアーキテクチャに適しています。

設計と実装が複雑になる場合があります。

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

CDC は、データベース内のデータが変更されたときにその変更をキャプチャする技術です。キャプチャされた変更は、その後、ダウンストリームシステムで処理するために使用することができます。

データ変更のリアルタイムな処理が可能
監査追跡に最適
データウェアハウスやデータレイクの更新に役立つ

パフォーマンスへの影響が大きくなる場合があります。

どの方法を選択すべきかは、要件によって異なります。単純なタスクの場合は、トリガーが適切な選択となるでしょう。より複雑なロジックや、スケーラブルで柔軟性の高いシステムが必要な場合は、ストアドプロシージャやイベント駆動型アーキテクチャが適しているかもしれません。CDC は、データ変更のリアルタイムな処理が必要な場合に適しています。

mysql triggers mariadb