SQLトリガーの包括的なガイド

概要: このチュートリアルでは、SQLトリガーの概念と、データベースシステムで簡単なトリガーを開発する方法について学びます。

SQLトリガーの概要

トリガーとは、データベース内のテーブルで発生した特定のイベントに応答して自動的に実行されるコードの一部です。

トリガーは常に特定のテーブルに関連付けられています。テーブルが削除されると、関連付けられているすべてのトリガーも自動的に削除されます。

トリガーは、以下のイベントの前または後に呼び出されます。

INSERT - 新しい行が挿入されたとき
UPDATE - 既存の行が更新されたとき
DELETE - 行が削除されたとき

INSERT、UPDATE、またはDELETEステートメントを発行すると、関係データベース管理システム（RDBMS）が対応するトリガーを発動します。

一部のRDBMSでは、トリガーはINSERT、UPDATE、またはDELETEステートメントを呼び出すステートメントの実行結果としても呼び出されます。たとえば、MySQLにはLOAD DATA INFILEがあり、テキストファイルから行を読み取り、非常に高速でテーブルに挿入し、BEFORE INSERTおよびAFTER INSERTトリガーを呼び出します。

一方、ステートメントはテーブル内の行を削除しても、関連するトリガーを呼び出さない場合があります。たとえば、TRUNCATE TABLEステートメントはテーブル内のすべての行を削除しますが、BEFORE DELETEおよびAFTER DELETEトリガーを呼び出しません。

トリガー作成ステートメントの構文

トリガーを作成するには、次のステートメントを使用します。

CREATE TRIGGER trigger_name [BEFORE|AFTER] event
ON table_name trigger_type
BEGIN
  -- trigger_logic
END;Code language: SQL (Structured Query Language) (sql)

構文について詳しく見ていきましょう。

最初に、CREATE TRIGGER句の後にトリガーの名前を指定します。
次に、BEFOREまたはAFTERキーワードを使用して、特定のイベント（INSERT、UPDATE、またはDELETEなど）に応答してトリガーがいつ発生するかを決定します。
次に、トリガーがバインドされるテーブルの名前を指定します。
次に、FOR EACH ROWまたはFOR EACH STATEMENTのいずれかを使用して、トリガーのタイプを指定します。これについては、次のセクションで詳しく説明します。
最後に、トリガーのロジックをBEGIN ... ENDブロックに入れます。

BEGIN ENDブロックのコードを使用するだけでなく、次のようにストアドプロシージャを実行することもできます。

CREATE TRIGGER trigger_name 
[BEFORE|AFTER] event
ON table_name trigger_type
EXECUTE stored_procedure_name;Code language: SQL (Structured Query Language) (sql)

行レベルトリガーとステートメントレベルトリガー

トリガーには、行レベルトリガーとステートメントレベルトリガーの2種類があります。

行レベルトリガーは、UPDATEステートメントによって行が影響を受けるたびに実行されます。UPDATEステートメントが10行に影響を与える場合、行レベルトリガーは10回実行されます（行ごとに1回）。UPDATEステートメントがどの行にも影響を与えない場合、行レベルトリガーはまったく実行されません。

行レベルトリガーとは異なり、ステートメントレベルトリガーは、UPDATEステートメントによって影響を受ける行数に関係なく、1回だけ呼び出されます。UPDATEステートメントがどの行にも影響を与えなかった場合でも、トリガーは実行されることに注意してください。

トリガーを作成するときは、FOR EACH ROWまたはFOR EACH STATEMENTをそれぞれ使用して、トリガーが行レベルかステートメントレベルかを指定できます。

SQLトリガーの使用法

通常、トリガーは次のシナリオで使用します。

テーブルの変更をログに記録します。一部のテーブルには、顧客のメールや従業員の給与など、すべての変更をログに記録したい機密データが含まれています。この場合、UPDATEトリガーを作成して、変更を別のテーブルに挿入できます。
データの複雑な整合性を強制します。このシナリオでは、トリガーを定義してデータを検証し、必要に応じてデータを再フォーマットできます。たとえば、BEFORE INSERTまたはBEFORE UPDATEトリガーを使用して、挿入または更新前にデータを変換できます。

SQLトリガーの例

デモンストレーションには、サンプルデータベースのemployeesテーブルを使用します。

salary列の値の変更をログに記録したいとします。これを行うには、変更を保存するための別のテーブルを作成し、トリガーを使用して変更をこのテーブルに挿入します。

次のステートメントは、salary_changesテーブルを作成します。

CREATE TABLE salary_changes (
    employee_id INT,
    changed_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    old_salary DECIMAL(8 , 2 ),
    new_salary DECIMAL(8 , 2 ),
    PRIMARY KEY (employee_id , changed_at)
);Code language: SQL (Structured Query Language) (sql)

salary_changesテーブルは、従業員ID、古い給与、新しい給与、および変更時刻を記録します。change_at列は、変更が発生した時刻を記録するために、デフォルトとして現在の時刻を使用することに注意してください。

次のbefore_update_salaryトリガーは、給与の変更をsalary_changesテーブルに記録します。

CREATE TRIGGER before_update_salary
BEFORE UPDATE ON employees
FOR EACH ROW
BEGIN
   IF NEW.salary <> OLD.salary THEN
	INSERT INTO salary_changes(employee_id,old_salary,new_salary)
        VALUES(NEW.employee_id,OLD.salary,NEW.salary);
    END IF;
END;
Code language: SQL (Structured Query Language) (sql)

トリガーの本体では、新しい給与が古い給与と異なる場合に、変更を挿入します。

トリガー本体内では、OLDおよびNEWキーワードを使用して、トリガーの影響を受ける行の列にアクセスすることに注意してください。

IDが102の従業員の給与を5％引き上げて、トリガーをテストしましょう。

最初に、従業員102の現在の給与を確認します。

SELECT 
    employee_id, 
    first_name, 
    last_name, 
    salary
FROM
    employees
WHERE
    employee_id = 110;Code language: SQL (Structured Query Language) (sql)

次に、次のUPDATEステートメントを発行して、給与を5％引き上げます。

UPDATE employees 
SET 
    salary = salary * 1.05
WHERE
    employee_id = 110;Code language: SQL (Structured Query Language) (sql)

第三に、salary_changesテーブルを調べて、トリガーが呼び出されたかどうかを確認します。

SELECT 
    *
FROM
    salary_changes;Code language: SQL (Structured Query Language) (sql)

ご覧のとおり、salary_changesテーブルには新しいエントリがあります。これは、トリガーが正しく呼び出されたことを意味します。

トリガーの変更

トリガーの定義を変更するには、CREATE OR REPLACE TRIGGERステートメントを使用します。

基本的に、CREATE OR REPLACE TRIGGERは、存在しない場合は新しいトリガーを作成し、存在する場合はトリガーを変更します。

CREATE OR REPLACE TRIGGERステートメントは、次のようにCREATE TRIGGERステートメントに似ています。

CREATE OR REPLACE TRIGGER trigger_name 
[BEFORE|AFTER] event
ON table_name trigger_type
BEGIN
  -- trigger_logic
END;Code language: SQL (Structured Query Language) (sql)

トリガーの削除

トリガーを削除するには、次のようにDROP TRIGGERステートメントを使用します。

DROP TRIGGER [IF EXISTS] trigger_name;Code language: SQL (Structured Query Language) (sql)

IF EXISTSオプションを使用すると、トリガーが存在する場合にトリガーを削除できます。トリガーが存在しない場合、ステートメントは何も行いません。ただし、IF EXISTSオプションがない場合、データベースシステムは、存在しないトリガーを削除しようとするとエラーを発行する可能性があります。

また、テーブルを削除すると、テーブルに関連付けられているすべてのトリガーも削除されます。次のステートメントは、before_update_salaryトリガーを削除します。

DROP TRIGGER IF EXISTS before_update_salary;Code language: SQL (Structured Query Language) (sql)

これで、SQLトリガーを十分に理解し、データベースシステムでトリガーを作成する方法がわかったはずです。