MySQL - トリガーのソースを表示

== 概要 ==
MySQLのトリガー (Trigger) は、テーブルに対する <code>INSERT</code>、<code>UPDATE</code>、<code>DELETE</code> の操作が行われた際に、自動的に実行される特別なストアドプロシージャである。<br>
トリガーは、データの整合性チェック、自動値設定、監査ログの記録、集計テーブルの更新等、様々な用途に利用される。<br>
<br>
トリガーには、<u>BEFOREトリガー</u> と <u>AFTERトリガー</u> の2種類のタイミングがある。<br>
* BEFOREトリガー
*: 行の変更が行われる前に実行され、データの検証や値の変更に使用される。
* AFTERトリガー
*: 行の変更が行われた後に実行され、監査ログの記録や集計テーブルの更新に使用される。
<br>
<u>トリガーは、<code>INSERT</code>、<code>UPDATE</code>、<code>DELETE</code> の3種類のイベントに対応している。</u><br>
<u>各トリガーは、NEW疑似レコード (新しい値) および OLD疑似レコード (変更前の値) を使用して、変更されるデータにアクセスできる。</u><br>
<br>
MySQLでは、同一テーブル・同一タイミング・同一イベントの複数トリガーが存在する場合、FOLLOWS句とPRECEDES句を使用して実行順序を制御できる。<br>
<br>
トリガーは、FOR EACH ROW (行レベルトリガー) のみをサポートしており、ステートメントレベルトリガーはサポートされていない。<br>
つまり、影響を受ける各行に対して1回ずつトリガーが実行される。<br>
<br>
<u>トリガーにはいくつかの制限事項があり、トリガー内から同じテーブルへの変更操作は不可、トリガー内でのCOMMIT / ROLLBACKは不可などの制約がある。</u><br>
<br><br>

== トリガーの作成 ==
==== 基本構文 ====
トリガーを作成するには、<code>CREATE TRIGGER</code>文を使用する。<br>
<br>
 <syntaxhighlight lang="mysql">
 CREATE
    [DEFINER = user]
    TRIGGER [IF NOT EXISTS] <トリガー名>
    <トリガータイミング> <トリガーイベント>
    ON <テーブル名> FOR EACH ROW
    [<トリガー順序>]
    <トリガー本体>
 
 trigger_time: { BEFORE | AFTER }
 
 trigger_event: { INSERT | UPDATE | DELETE }
 
 trigger_order: { FOLLOWS | PRECEDES } other_trigger_name
 </syntaxhighlight>
<br>
<center>
{| class="wikitable"
|+ CREATE TRIGGER文の主要な構成要素
! 要素 !! 説明
|-
| DEFINER || トリガーの定義者を指定する。<br>省略した場合は、現在のユーザが定義者になる。
|-
| IF NOT EXISTS || トリガーが既に存在する場合にエラーにならず、警告のみが出力される。<br>(MySQL 8.0.29以降)
|-
| trigger_name || トリガー名を指定する。<br>データベース内で一意である必要がある。
|-
| trigger_time || トリガーの実行タイミングを指定する。<br><code>BEFORE</code> または <code>AFTER</code>
|-
| trigger_event || トリガーを起動するイベントを指定する。<br><code>INSERT</code>、<code>UPDATE</code>、<code>DELETE</code>
|-
| tbl_name || トリガーを設定するテーブル名を指定する。
|-
| FOR EACH ROW || 行レベルトリガーであることを示す。<br>MySQLでは必須
|-
| trigger_order || 同一タイミング・同一イベントの複数トリガーがある場合の実行順序を指定する。<br><code>FOLLOWS</code> または <code>PRECEDES</code>
|-
| trigger_body || トリガーで実行する処理を記述する。<br>複数のSQL文を実行する場合は、BEGINとENDで囲む。
|}
</center>
<br>
簡潔なトリガーの例を以下に示す。<br>
<br>
 <syntaxhighlight lang="mysql">
 DELIMITER //
 
 CREATE TRIGGER users_before_insert
 BEFORE INSERT ON users
 FOR EACH ROW
 BEGIN
    SET NEW.created_at = NOW();
 END//
 
 DELIMITER ;
 </syntaxhighlight>
<br>
<code>DELIMITER</code> コマンドは、SQL文の区切り文字を一時的に変更するために使用される。<br>
トリガー内にセミコロンを含む場合、デフォルトの区切り文字 (セミコロン) では正しく解析されないため、一時的に別の区切り文字 (通常は //) に変更する。<br>
<br>
==== トリガータイミング ====
トリガータイミングには、BEFOREとAFTERの2種類がある。<br>
<br>
<center>
{| class="wikitable"
|+ トリガータイミングの比較
! タイミング !! 実行タイミング !! 用途 !! NEW.col_nameへの代入
|-
| BEFORE || 行の変更が行われる前に実行される || データの検証、値の自動変更 || 可能
|-
| AFTER || 行の変更が行われた後に実行される || 監査ログの記録、集計テーブルの更新 || 不可 (読み取り専用)
|}
</center>
<br>
BEFOREトリガーでは、<u>SET NEW.col_name = value</u> の構文を使用して、挿入または更新される値を変更できる。<br>
AFTERトリガーでは、NEWおよびOLD疑似レコードは読み取り専用であり、値を変更できない。<br>
<br>
==== トリガーイベント ====
トリガーイベントには、INSERT、UPDATE、DELETEの3種類がある。<br>
<br>
<center>
{| class="wikitable"
|+ トリガーイベントとNEW / OLDの使用可否
! イベント !! 説明 !! NEW !! OLD
|-
| INSERT || 新しい行が挿入されるときに実行される || 使用可能 || 使用不可
|-
| UPDATE || 行が更新されるときに実行される || 使用可能 || 使用可能
|-
| DELETE || 行が削除されるときに実行される || 使用不可 || 使用可能
|}
</center>
<br>
* INSERTトリガー
*: NEWのみが使用可能であり、挿入される新しい値を参照できる。
* UPDATEトリガー
*: NEWとOLDの両方が使用可能であり、変更前の値と変更後の値の両方を参照できる。
* DELETEトリガー
*: OLDのみが使用可能であり、削除される行の値を参照できる。
<br>
==== NEWとOLD疑似レコード ====
トリガー内では、NEWとOLDという特別な疑似レコードを使用して、変更されるデータにアクセスできる。<br>
<br>
<center>
{| class="wikitable"
|+ NEWとOLD疑似レコード
! 疑似レコード !! 説明 !! 使用可能なイベント !! 書き込み可能
|-
| NEW.column_name || INSERT / UPDATEで使用する。<br>新しい値を参照する。 || INSERT、UPDATE || BEFOREトリガーのみ可能
|-
| OLD.column_name || UPDATE / DELETEで使用する。<br>変更前の値を参照する。 || UPDATE、DELETE || 不可 (読み取り専用)
|}
</center>
<br>
BEFOREトリガーでは、<u>SET NEW.col_name = value</u> の構文を使用して、挿入または更新される値を変更できる。<br>
<br>
NEWとOLDの使用例を以下に示す。<br>
<br>
 <syntaxhighlight lang="mysql">
 DELIMITER //
 
 CREATE TRIGGER update_audit BEFORE UPDATE ON products
 FOR EACH ROW
 BEGIN
    -- 価格が変更された場合のみ、変更日時を更新
    IF NEW.price != OLD.price THEN
       SET NEW.updated_at = NOW();
    END IF;
 END//
 
 DELIMITER ;
 </syntaxhighlight>
<br><br>

== トリガーの変更と削除 ==
MySQLでは、<code>ALTER TRIGGER</code> 文は存在しない。<br>
トリガーを変更するには、<code>DROP TRIGGER</code> 文で削除してから、<code>CREATE TRIGGER</code> 文で再作成する必要がある。<br>
<br>
トリガーを削除するには、<code>DROP TRIGGER</code> 文を使用する。<br>
<br>
 <syntaxhighlight lang="mysql">
 DROP TRIGGER [IF EXISTS] [<スキーマ名>.]<トリガー名>;
 </syntaxhighlight>
<br>
<code>IF EXISTS</code> 句を使用すると、トリガーが存在しない場合でもエラーにならず、警告のみが出力される。<br>
<br>
* トリガー削除の例
*: <syntaxhighlight lang="mysql">
 DROP TRIGGER IF EXISTS users_before_insert;
 </syntaxhighlight>
*: <br>
* スキーマ名を指定してトリガーを削除する例
*: <syntaxhighlight lang="mysql">
 DROP TRIGGER IF EXISTS mydb.users_before_insert;
 </syntaxhighlight>
*: <br>
* トリガーを変更するには、削除してから再作成する。
*: <syntaxhighlight lang="mysql">
 DROP TRIGGER IF EXISTS users_before_insert;
 
 DELIMITER //
 
 CREATE TRIGGER users_before_insert
 BEFORE INSERT ON users
 FOR EACH ROW
 BEGIN
    SET NEW.email = LOWER(TRIM(NEW.email));
    SET NEW.created_at = NOW();
 END//
 
 DELIMITER ;
 </syntaxhighlight>
<br><br>

== トリガーの確認 ==
トリガーの情報を確認するには、複数の方法がある。<br>
<br>
* トリガー一覧を確認する。
*: <syntaxhighlight lang="mysql">
 SHOW TRIGGERS [FROM <データベース名>] [LIKE 'pattern'];
 </syntaxhighlight>
*: <br>
* 現在のデータベースのトリガー一覧を表示する例
*: <syntaxhighlight lang="mysql">
 SHOW TRIGGERS;
 </syntaxhighlight>
*: <br>
* 特定のデータベースのトリガー一覧を表示する例
*: <syntaxhighlight lang="mysql">
 SHOW TRIGGERS FROM mydb;
 </syntaxhighlight>
*: <br>
* パターンマッチングで特定のトリガーを検索する例
*: <syntaxhighlight lang="mysql">
 SHOW TRIGGERS LIKE 'users%';
 </syntaxhighlight>
*: <br>
* トリガーの定義を確認する。
*: <code>SHOW CREATE TRIGGER</code> 文は、トリガーの完全な定義 (CREATE TRIGGER文) を表示する。
*: <syntaxhighlight lang="mysql">
 SHOW CREATE TRIGGER <トリガー名>;
 </syntaxhighlight>
*: <br>
* <code>INFORMATION_SCHEMA.TRIGGERS</code> テーブルから詳細情報を取得する。
*: <syntaxhighlight lang="mysql">
 SELECT TRIGGER_NAME, EVENT_MANIPULATION, EVENT_OBJECT_TABLE,
        ACTION_STATEMENT, ACTION_TIMING, ACTION_ORDER
 FROM INFORMATION_SCHEMA.TRIGGERS
 WHERE TRIGGER_SCHEMA = '<データベース名>';
 </syntaxhighlight>
<br>
<center>
{| class="wikitable"
|+ INFORMATION_SCHEMA.TRIGGERSテーブルの主要カラム
! カラム名 !! 説明
|-
| <code>TRIGGER_SCHEMA</code> || トリガーが定義されているデータベース名
|-
| <code>TRIGGER_NAME</code> || トリガー名
|-
| <code>EVENT_MANIPULATION</code> || トリガーイベント (INSERT、UPDATE、DELETE)
|-
| <code>EVENT_OBJECT_SCHEMA</code> || トリガーが設定されているテーブルのデータベース名
|-
| <code>EVENT_OBJECT_TABLE</code> || トリガーが設定されているテーブル名
|-
| <code>ACTION_ORDER</code> || 同一タイミング・同一イベントの複数トリガーがある場合の実行順序
|-
| <code>ACTION_STATEMENT</code> || トリガーで実行される処理の内容
|-
| <code>ACTION_TIMING</code> || トリガーのタイミング (BEFORE、AFTER)
|-
| <code>CREATED</code> || トリガーの作成日時
|}
</center>
<br>
特定のテーブルに設定されているトリガーを確認する例を以下に示す。<br>
<br>
 <syntaxhighlight lang="mysql">
 SELECT TRIGGER_NAME, ACTION_TIMING, EVENT_MANIPULATION, ACTION_ORDER
 FROM INFORMATION_SCHEMA.TRIGGERS
 WHERE EVENT_OBJECT_SCHEMA = 'mydb'
   AND EVENT_OBJECT_TABLE = 'users'
 ORDER BY ACTION_TIMING, EVENT_MANIPULATION, ACTION_ORDER;
 </syntaxhighlight>
<br><br>

== トリガーの順序 ==
==== FOLLOWS句 と PRECEDES句 ====
MySQL 5.7以降では、同一テーブル・同一タイミング・同一イベントの複数トリガーが存在する場合、FOLLOWS句とPRECEDES句を使用して実行順序を制御できる。<br>
<br>
<center>
{| class="wikitable"
|+ FOLLOWS句とPRECEDES句
! 構文 !! 説明
|-
| FOLLOWS other_trigger
| 指定したトリガー (other_trigger) の後に実行される
|-
| PRECEDES other_trigger
| 指定したトリガー (other_trigger) の前に実行される
|}
</center>
<br>
<u>FOLLOWS句とPRECEDES句を指定しない場合、最後に作成されたトリガーが最初に実行される (後入れ先出し) 動作になる。</u><br>
<br>
トリガーの実行順序を制御する例を以下に示す。<br>
<br>
 <syntaxhighlight lang="mysql">
 DELIMITER //
 
 -- 最初のトリガー
 CREATE TRIGGER users_before_insert_1
 BEFORE INSERT ON users
 FOR EACH ROW
 BEGIN
    SET NEW.email = LOWER(TRIM(NEW.email));
 END//
 
 -- 2番目のトリガー (users_before_insert_1の後に実行)
 CREATE TRIGGER users_before_insert_2
 BEFORE INSERT ON users
 FOR EACH ROW
 FOLLOWS users_before_insert_1
 BEGIN
    SET NEW.created_at = NOW();
 END//
 
 -- 3番目のトリガー (users_before_insert_2の前に実行)
 CREATE TRIGGER users_before_insert_3
 BEFORE INSERT ON users
 FOR EACH ROW
 PRECEDES users_before_insert_2
 BEGIN
    -- 検証処理
    IF NEW.age < 0 OR NEW.age > 150 THEN
       SIGNAL SQLSTATE '45000'
          SET MESSAGE_TEXT = 'Age must be between 0 and 150';
    END IF;
 END//
 
 DELIMITER ;
 </syntaxhighlight>
<br>
上記の例では、トリガーの実行順序は以下のようになる。<br>
# <u>users_before_insert_1</u> (最初)
# <u>users_before_insert_3</u> (users_before_insert_2の前)
# <u>users_before_insert_2</u> (users_before_insert_1の後)
<br>
<code>ACTION_ORDER</code> カラムで実行順序を確認できる。<br>
<br>
 <syntaxhighlight lang="mysql">
 SELECT TRIGGER_NAME, ACTION_TIMING, EVENT_MANIPULATION, ACTION_ORDER
 FROM INFORMATION_SCHEMA.TRIGGERS
 WHERE EVENT_OBJECT_TABLE = 'users'
   AND ACTION_TIMING = 'BEFORE'
   AND EVENT_MANIPULATION = 'INSERT'
 ORDER BY ACTION_ORDER;
 </syntaxhighlight>
<br>
<code>ACTION_ORDER</code> カラムの値は、実行順序を示す数値である。<br>
値が小さいほど、先に実行される。<br>
<br><br>

== BEFOREトリガーとAFTERトリガーの使い分け ==
BEFOREトリガーとAFTERトリガーは、用途に応じて使い分ける必要がある。<br>
<br>
<center>
{| class="wikitable"
|+ BEFOREトリガーとAFTERトリガーの使い分け
! タイミング !! 主な用途 !! 特徴
|-
| BEFORE
|
* データの検証
* 値の自動変更 / 正規化
* デフォルト値の設定
* 制約のチェック
|
* <code>SET NEW.col_name = value</code>で値を変更可能
* エラーを発生させることで操作を中断可能
|-
| AFTER
|
* 監査ログの記録
* 集計テーブルの更新
* 他のテーブルへのデータ連携
* 通知の送信
|
* NEW / OLDは読み取り専用
* データ変更後の処理に適している
* 他のテーブルへの書き込みが可能
|}
</center>
<br>
BEFOREトリガーは、データが実際にテーブルに書き込まれる前に実行されるため、データの検証や値の変更に適している。<br>
BEFOREトリガー内でSIGNAL文を使用してエラーを発生させることで、操作を中断できる。<br>
<br>
AFTERトリガーは、データが既にテーブルに書き込まれた後に実行されるため、監査ログの記録や集計テーブルの更新に適している。<br>
AFTERトリガーでは、NEW / OLD疑似レコードは読み取り専用であり、値を変更できない。<br>
<br>
BEFOREトリガーで操作を中断する例を以下に示す。<br>
<br>
 <syntaxhighlight lang="mysql">
 DELIMITER //
 
 CREATE TRIGGER validate_age BEFORE INSERT ON users
 FOR EACH ROW
 BEGIN
    IF NEW.age < 0 OR NEW.age > 150 THEN
       SIGNAL SQLSTATE '45000'
          SET MESSAGE_TEXT = 'Age must be between 0 and 150';
    END IF;
 END//
 
 DELIMITER ;
 </syntaxhighlight>
<br>
<u>SIGNAL SQLSTATE '45000'</u> は、ユーザー定義例外の汎用コードであり、エラーを発生させて操作を中断する。<br>
MESSAGE_TEXTには、エラーメッセージを指定する。<br>
<br><br>

== サンプルクエリ ==
==== データ検証 ====
BEFOREトリガーを使用して、データの妥当性を検証する例を以下に示す。<br>
<br>
以下の例では、年齢が0未満または150を超える場合にエラーを発生させ、INSERT操作を中断する。<br>
<br>
 <syntaxhighlight lang="mysql">
 DELIMITER //
 
 CREATE TRIGGER validate_age BEFORE INSERT ON users
 FOR EACH ROW
 BEGIN
    IF NEW.age < 0 OR NEW.age > 150 THEN
       SIGNAL SQLSTATE '45000'
          SET MESSAGE_TEXT = 'Age must be between 0 and 150';
    END IF;
 END//
 
 DELIMITER ;
 </syntaxhighlight>
<br>
複数の条件を検証する例を以下に示す。<br>
<br>
 <syntaxhighlight lang="mysql">
 DELIMITER //
 
 CREATE TRIGGER validate_product BEFORE INSERT ON products
 FOR EACH ROW
 BEGIN
    -- 価格の検証
    IF NEW.price <= 0 THEN
       SIGNAL SQLSTATE '45000'
          SET MESSAGE_TEXT = 'Price must be greater than 0';
    END IF;
 
    -- 在庫数の検証
    IF NEW.stock < 0 THEN
       SIGNAL SQLSTATE '45000'
          SET MESSAGE_TEXT = 'Stock cannot be negative';
    END IF;

    -- 商品コードの検証
    IF LENGTH(NEW.product_code) != 8 THEN
       SIGNAL SQLSTATE '45000'
          SET MESSAGE_TEXT = 'Product code must be 8 characters';
    END IF;
 END//
 
 DELIMITER ;
 </syntaxhighlight>
<br>
==== 自動値設定 ====
BEFOREトリガーを使用して、値を自動的に設定または正規化する例を以下に示す。<br>
<br>
 <syntaxhighlight lang="mysql">
 DELIMITER //
 
 CREATE TRIGGER users_before_insert BEFORE INSERT ON users
 FOR EACH ROW
 BEGIN
    -- メールアドレスを小文字に変換し、前後の空白を削除
    SET NEW.email = LOWER(TRIM(NEW.email));
 
    -- 作成日時を自動設定
    SET NEW.created_at = NOW();
 END//
 
 DELIMITER ;
 </syntaxhighlight>
<br>
UPDATEトリガーで更新日時を自動設定する例を以下に示す。<br>
<br>
 <syntaxhighlight lang="mysql">
 DELIMITER //
 
 CREATE TRIGGER users_before_update BEFORE UPDATE ON users
 FOR EACH ROW
 BEGIN
    SET NEW.updated_at = NOW();
 END//
 
 DELIMITER ;
 </syntaxhighlight>
<br>
条件付きで値を自動設定する例を以下に示す。<br>
<br>
 <syntaxhighlight lang="mysql">
 DELIMITER //
 
 CREATE TRIGGER products_before_update BEFORE UPDATE ON products
 FOR EACH ROW
 BEGIN
    -- 価格が変更された場合のみ、価格変更日時を更新
    IF NEW.price != OLD.price THEN
       SET NEW.price_updated_at = NOW();
    END IF;
 END//
 
 DELIMITER ;
 </syntaxhighlight>
<br>
==== 監査ログの記録 ====
AFTERトリガーを使用して、データ変更の監査ログを記録する例を以下に示す。<br>
<br>
まず、監査ログ用のテーブルを作成する。<br>
<br>
 <syntaxhighlight lang="mysql">
 CREATE TABLE audit_log (
    log_id INT AUTO_INCREMENT PRIMARY KEY,
    table_name VARCHAR(100),
    action VARCHAR(10),
    old_values TEXT,
    new_values TEXT,
    changed_by VARCHAR(100),
    changed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
 );
 </syntaxhighlight>
<br>
UPDATEトリガーで変更履歴を記録する例を以下に示す。<br>
<br>
 <syntaxhighlight lang="mysql">
 DELIMITER //
 
 CREATE TRIGGER audit_update AFTER UPDATE ON employees
 FOR EACH ROW
 BEGIN
    INSERT INTO audit_log (table_name, action, old_values, new_values, changed_by)
    VALUES ('employees', 'UPDATE',
       CONCAT('name=', OLD.name, ',salary=', OLD.salary),
       CONCAT('name=', NEW.name, ',salary=', NEW.salary),
       USER());
 END//
 
 DELIMITER ;
 </syntaxhighlight>
<br>
<code>USER()</code> 関数は、現在接続しているユーザ名を返す。<br>
<br>
INSERTとDELETEの監査ログを記録する例を以下に示す。<br>
<br>
 <syntaxhighlight lang="mysql">
 DELIMITER //
 
 CREATE TRIGGER audit_insert AFTER INSERT ON employees
 FOR EACH ROW
 BEGIN
    INSERT INTO audit_log (table_name, action, new_values, changed_by)
    VALUES ('employees', 'INSERT',
       CONCAT('id=', NEW.id, ',name=', NEW.name, ',salary=', NEW.salary),
       USER());
 END//
 
 CREATE TRIGGER audit_delete AFTER DELETE ON employees
 FOR EACH ROW
 BEGIN
    INSERT INTO audit_log (table_name, action, old_values, changed_by)
    VALUES ('employees', 'DELETE',
       CONCAT('id=', OLD.id, ',name=', OLD.name, ',salary=', OLD.salary),
       USER());
 END//
 
 DELIMITER ;
 </syntaxhighlight>
<br>
==== 集計テーブルの更新 ====
AFTERトリガーを使用して、集計テーブルを自動的に更新する例を以下に示す。<br>
<br>
注文テーブルと注文明細テーブルがあり、注文明細が挿入されたときに注文の合計金額を自動更新する。<br>
<br>
 <syntaxhighlight lang="mysql">
 CREATE TABLE orders (
    id INT PRIMARY KEY,
    customer_id INT,
    total_amount DECIMAL(10,2) DEFAULT 0.00
 );
 
 CREATE TABLE order_items (
    id INT PRIMARY KEY,
    order_id INT,
    product_id INT,
    quantity INT,
    price DECIMAL(10,2),
    FOREIGN KEY (order_id) REFERENCES orders(id)
 );
 </syntaxhighlight>
<br>
注文明細が挿入されたときに、注文の合計金額を更新するトリガーを以下に示す。<br>
<br>
 <syntaxhighlight lang="mysql">
 DELIMITER //
 
 CREATE TRIGGER update_order_total AFTER INSERT ON order_items
 FOR EACH ROW
 BEGIN
    UPDATE orders
    SET total_amount = (
       SELECT SUM(quantity * price)
       FROM order_items
       WHERE order_id = NEW.order_id
    )
    WHERE id = NEW.order_id;
 END//
 
 DELIMITER ;
 </syntaxhighlight>
<br>
注文明細が更新または削除された時にも、合計金額を更新するトリガーを以下に示す。<br>
<br>
 <syntaxhighlight lang="mysql">
 DELIMITER //
 
 CREATE TRIGGER update_order_total_on_update AFTER UPDATE ON order_items
 FOR EACH ROW
 BEGIN
    UPDATE orders
    SET total_amount = (
       SELECT SUM(quantity * price)
       FROM order_items
       WHERE order_id = NEW.order_id
    )
    WHERE id = NEW.order_id;
 END//
 
 CREATE TRIGGER update_order_total_on_delete AFTER DELETE ON order_items
 FOR EACH ROW
 BEGIN
    UPDATE orders
    SET total_amount = (
       SELECT COALESCE(SUM(quantity * price), 0.00)
       FROM order_items
       WHERE order_id = OLD.order_id
    )
    WHERE id = OLD.order_id;
 END//
 
 DELIMITER ;
 </syntaxhighlight>
<br>
<code>COALESCE</code> 関数は、最初のNULL以外の値を返す。<br>
全ての注文明細が削除された場合、SUM関数はNULLを返すため、COALESCEで0.00を返すようにしている。<br>
<br><br>

== トリガーのデバッグ ==
トリガーのデバッグは、通常のストアドプロシージャと比べて難しい場合がある。<br>
トリガーは自動的に実行されるため、エラーが発生した場合の原因特定が困難である。<br>
<br>
デバッグ用のテーブルを作成して、トリガーの実行をログに記録する方法がある。<br>
<br>
 <syntaxhighlight lang="mysql">
 CREATE TABLE trigger_debug_log (
    id INT AUTO_INCREMENT PRIMARY KEY,
    trigger_name VARCHAR(100),
    debug_info TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
 );
 </syntaxhighlight>
<br>
トリガー内にデバッグ情報を記録する例を以下に示す。<br>
<br>
 <syntaxhighlight lang="mysql">
 DELIMITER //
 
 CREATE TRIGGER users_before_insert_debug BEFORE INSERT ON users
 FOR EACH ROW
 BEGIN
    -- デバッグ情報を記録
    INSERT INTO trigger_debug_log (trigger_name, debug_info)
    VALUES ('users_before_insert', CONCAT('email=', NEW.email, ',age=', NEW.age));
 
    -- 通常の処理
    SET NEW.email = LOWER(TRIM(NEW.email));
    SET NEW.created_at = NOW();
 END//
 
 DELIMITER ;
 </syntaxhighlight>
<br>
開発環境では、<code>general_log</code> を有効にしてクエリログを確認することもできる。<br>
<br>
 <syntaxhighlight lang="mysql">
 SET GLOBAL general_log = 1;
 </syntaxhighlight>
<br>
general_logファイルの場所を確認する。<br>
<br>
 <syntaxhighlight lang="mysql">
 SHOW VARIABLES LIKE 'general_log_file';
 </syntaxhighlight>
<br>
<u>本番環境では、<code>general_log</code> はパフォーマンスに影響を与えるため、通常は無効にしておく。</u><br>
<br><br>

== トリガーの制限事項 ==
MySQLのトリガーには、いくつかの制限事項がある。<br>
<br>
<center>
{| class="wikitable"
|+ トリガーの主な制限事項
! 制限事項 !! 説明
|-
| 同一テーブルへの変更不可 || トリガー内から、トリガーが設定されているテーブルへのINSERT / UPDATE / DELETEは実行できない。<br>これを行うと、再帰的なトリガー呼び出しが発生し、エラーになる。
|-
| COMMIT / ROLLBACK不可 || トリガー内で、COMMIT、ROLLBACK、START TRANSACTIONを実行できない。<br>トリガーは呼び出し元のトランザクション内で実行される。
|-
| RETURN文不可 || トリガー内でRETURN文を使用できない。<br>ストアドファンクションとは異なり、トリガーは値を返さない。
|-
| VIEWへのトリガー不可 || VIEWに対してトリガーを作成できない。<br>トリガーは、テーブルに対してのみ作成可能である。
|-
| LOCK TABLES不可 || トリガー内で <code>LOCK TABLES</code> および <code>UNLOCK TABLES</code> を使用できない。
|-
| ストアドプロシージャの制限 || トリガー内からCALLでストアドプロシージャを呼び出す場合、<br>そのプロシージャ内でもトリガーと同じ制限が適用される。
|-
| 直接再帰不可 || トリガーは直接再帰呼び出しできない。<br>ただし、カスケードによる間接的な呼び出しは可能である。
|-
| LOAD DATAでのパフォーマンス || <code>LOAD DATA</code>文では、BEFOREトリガーが行ごとに実行されるため、<br>大量データのロード時にパフォーマンスに影響を与える。
|}
</center>
<br>
トリガー内から同一テーブルを変更しようとすると、エラーが発生する。<br>
<br>
 <syntaxhighlight lang="mysql">
 DELIMITER //
 
 -- これはエラーになる
 CREATE TRIGGER invalid_trigger AFTER INSERT ON users
 FOR EACH ROW
 BEGIN
    -- 同一テーブルへのINSERTは不可
    INSERT INTO users (username) VALUES ('test');  -- エラー
 END//
 
 DELIMITER ;
 </syntaxhighlight>
<br>
このような場合は、別のテーブルに書き込むか、BEFOREトリガーで値を変更する方法を検討する。<br>
<br>
トリガー内でトランザクション制御を行おうとすると、エラーが発生する。<br>
<br>
 <syntaxhighlight lang="mysql">
 DELIMITER //
 
 -- これはエラーになる
 CREATE TRIGGER invalid_trigger AFTER INSERT ON users
 FOR EACH ROW
 BEGIN
    START TRANSACTION;  -- エラー
    INSERT INTO audit_log VALUES (...);
    COMMIT;  -- エラー
 END//
 
 DELIMITER ;
 </syntaxhighlight>
<br>
トリガーは、呼び出し元のトランザクション内で実行されるため、トランザクション制御は呼び出し元で行う。<br>
<br><br>

== 関連ページ ==
* [[MySQL - ストアドプロシージャ]]
*: ストアドプロシージャの作成、実行、パラメータの使用方法
* [[MySQL - ストアドファンクション]]
*: ストアドファンクションの作成、戻り値の使用方法
* [[MySQL - イベントスケジューラ]]
*: 定期的なタスクの自動実行
<br><br>


{{#seo:
|title={{PAGENAME}} : Exploring Electronics and SUSE Linux | MochiuWiki
|keywords=MochiuWiki,Mochiu,Wiki,Mochiu Wiki,MySQL,Trigger,トリガー,CREATE TRIGGER,DROP TRIGGER,BEFORE,AFTER,INSERT,UPDATE,DELETE,NEW,OLD,FOLLOWS,PRECEDES,Database,データベース,SQL,ストアドプロシージャ,監査ログ,データ検証,自動値設定,集計テーブル,SIGNAL,FOR EACH ROW,電気回路,電子回路,基板,プリント基板
|description={{PAGENAME}} - MySQLのトリガーの作成、変更、削除、BEFOREトリガー、AFTERトリガー、NEW / OLD疑似レコード、実用例、デバッグ方法に関する総合ガイド | This page is {{PAGENAME}} in our wiki about electronic circuits and SUSE Linux
|image=/resources/assets/MochiuLogo_Single_Blue.png
}}

__FORCETOC__
[[カテゴリ:MySQL]]