MySQL のトリガー

ここでは MySQL のトリガーについてご説明します。

サンプルデータベースのテーブルを使いますので、実際にスクリプトを実行してみたい方は、こちら のスクリプトを実行して school_db データベースを作成しておいてください。

目次
  1. MySQL のトリガーについて
  2. MySQL のトリガーを作成する
  3. MySQL のトリガーを削除する

MySQL のトリガーについて

MySQL のテーブルにトリガーを生成すると、テーブルにレコードが挿入・更新・削除される度に、その前か後に指定したステートメントを実行することができます。

MySQL のトリガーを生成するには CREATE TRIGGER 文を使います。

CREATE TRIGGE 文の基本的な構文は次の通りです。

DELIMITER //
CREATE TRIGGER トリガー名
[ BEFORE | AFTER ] [ INSERT | UPDATE | DELETE ] 
ON テーブル名 FOR EACH ROW
([ FOLLOWS | PRECEDES ] 同じテーブルにある既存のトリガー名)
BEGIN
	SQL ステートメント
END//
DELIMITER ;

まず、トリガーの定義の中でデフォルトの DELIMITER の ; を使うので、DELIMITER を定義の中で使わない他の文字（ここでは //）に変更します。

[ BEFORE | AFTER ] では、次に指定する、INSERT・UPDATE・DELETE 文の前にトリガーで指定した SQL ステートメントを実行するのか、後で実行するのかを指定します。

[ INSERT | UPDATE | DELETE ] では、テーブルにデータが挿入された時にトリガーを実行するのか、データが更新された時に実行するのか、データが削除された時に実行するのかを指定します。

例えば、データ挿入前にトリガーを実行したいのであれば BEFORE INSERT、データ更新後に実行したいのであれば、AFTER UPDATE と指定します。


ON テーブル名 FOR EACH ROW で、トリガーを作成するテーブル名を指定します。


([ FOLLOWS | PRECEDES ] 同じテーブルにある既存のトリガー名) はオプショナルで、このテーブルに同じタイミングで実行されているトリガーがある場合に、今回生成するトリガーをそのトリガーよりも前に実行するのか、後に実行するのかを定義します。

今回生成するトリガーを後に実行したい時は FOLLOWS、先に実行したい時は PRECEDES を指定します。


SQL ステートメント内では、INSERT では挿入された値や、UPDATE では変更前後の値、DELETE では削除された値を OLD と NEW というエイリアスを使って取得することができます。

  • INSERT - 挿入された値: NEW.カラム名
  • UPDATE - 変更前の値: OLD.カラム名、変更後の値: NEW.カラム名
  • DELETE - 削除された値: OLD.カラム名

MySQL のトリガーを作成する

それでは、実際にトリガーを作成して、実行してみます。

今回は AFTER UPDATE トリガーを作成して、変更前後の値をログ用のテーブルに挿入します。


次のような students テーブルがあります。

MySQL のトリガー 1


追加で次のような student_number_log テーブルを作っておきます。

CREATE TABLE student_number_log (
	students_log_id INT NOT NULL AUTO_INCREMENT,
    student_id INT NOT NULL,
    student_number_from VARCHAR(10) NULL,
	student_number_to VARCHAR(10) NULL,
	updated_on DATETIME NOT NULL,
    PRIMARY KEY (students_log_id)
);

students テーブルの student_number が変更された時に、変更前後の値を student_number_log に挿入する AFTER UPDATE トリガーを生成するクエリーは次の通りです。

DROP TRIGGER IF EXISTS trg_students_log;
DELIMITER //
CREATE TRIGGER trg_students_log
	AFTER UPDATE
	ON students FOR EACH ROW
BEGIN

	IF OLD.student_number <> NEW.student_number THEN
    
		INSERT INTO student_number_log (
			student_id,
			student_number_from,
			student_number_to,
			updated_on
		)
		VALUES (
			NEW.student_id,
			OLD.student_number,
			NEW.student_number,
			NOW()
		);
        
	END IF;

END//
DELIMITER ;

OLD.student_number で変更前、NEW.student_number で変更後の student_number の値が取得できます。


このクエリーを実行すると、students テーブルに trg_students_log という名前のトリガーが作成されます。

MySQL Workbench の Schemas タブをリフレッシュすると、students テーブルの下の Triggers に trg_students_log ができていますね。

MySQL のトリガー 2


以下の UPDATE 文を使って students テーブルの student_id が 1 と 2 の学生の student_number の前に 'S' つけてみます。

UPDATE 	students
SET 	student_number = CONCAT('S', student_number)
WHERE 	student_id IN (1,2);

この UPDATE 文を実行した後に、student_number_log テーブルを確認すると、先ほど作成した AFTER UPDATE トリガーにより、UPDATE 文で更新される前後の student_number が次のように挿入されます。

MySQL のトリガー 3


MySQL のトリガーを削除する

MySQL でトリガーを削除するには、DROP TRIGGER 文を使います。

DROP TRIGGER 文の構文は次の通りです。

DROP TRIGGER トリガー名;

例えば、先ほど作成した trg_students_log という名前のトリガーを削除したいのであれば次のようにできます。

DROP TRIGGER trg_students_log;

このクエリーを実行すると trg_students_log トリガーが削除されます。

MySQL のトリガー 4


DROP TRIGGER 文は、実行した時に指定したトリガーが存在しないとエラーになります。

指定したトリガーが存在する時のみ削除したい場合は、IF EXISTS を次のように追加します。

DROP TRIGGER IF EXISTS トリガー名;

以上、MySQL のトリガーについてご説明しました。

© 2024 MySQL 入門