MySQL トリガの作成、確認、削除

MySQLのトリガの作成、確認、削除について説明します。

トリガを作成するとレコードの挿入や削除、更新時などに指定した動作を
させることができます。

以下のようなSQLコマンドを発行します。

CREATE TRIGGER トリガ名  {BEFORE | AFTER} {INSERT | UPDATE | DELETE}
 ON テーブル名 FOR EACH ROW
 BEGEN
 ～させたい処理～
 END;

ちょっと長いですがこのような書式でコードを書きます。
例えばINSERTする前に処理したい場合は「BEFORE INSERT」となり、
UPDATE後に処理したい場合は「AFTER UPDATE」と書くことになります。

実際のコードは以下のようになります。

delimiter //
create trigger test_trigger before update on test for each row
begin
     insert into backup(id) values(old.id);
end;
//
delimiter ;

begin～end内に書く処理の末尾には文の区切りを示すセミコロンが必要なのですが、
デリミタがセミコロンのままだと、セミコロンを入力した時点でトリガの作成が終了してしまうので、
あらかじめデリミタの設定を変えておく必要があります。
それが最初にある「delimiter //」というコマンドです。
「//」の部分は自分の好きな区切り文字を指定すれば良いです。
「end;」でさせたい処理の終了を示し、変更したデリミタ「//」を入力してトリガの作成は終了です。
最後にデリミタを元の「;」に戻しています。

またbegin～end内では対象テーブルの更新前のデータや更新後のデータを参照できる、
newとoldという特殊な指定が可能です。
newはinsertおよびupdateで使用可能で、oldはupdate及びdelete時に使用可能です。
上記のコードだと「old.id」で、testテーブルのupdate更新前のidカラムの値を設定していることになります。
つまり、testテーブルでupdateが行われる前にこのトリガが実行されるため、
backupテーブルに変更前のデータが保存されることになります。

このようにトリガを作成すると上記のような簡易バックアップも作成できます。

トリガの確認、削除

作成したトリガを確認したり、削除するには以下のコマンドを発行します。

//トリガの確認
show triggers;
//トリガの削除
drop trigger トリガ名

「show triggers」を実行すると以下のような結果が返ってきます。

             Trigger: list_before_delete
               Event: DELETE
               Table: list
           Statement: begin
insert into list_bk values(old.id,old.no);
end
              Timing: BEFORE
             Created: NULL
            sql_mode: NO_AUTO_VALUE_ON_ZERO
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: utf8_general_ci

上記のようにトリガの名前や、イベント、タイミング、実行される処理などが確認できます。
トリガの削除は上記のようにトリガ名を指定するだけで削除できます。

>> 【MySQL ユーザの作成＆削除】に進む
>> MySQL入門トップに戻る

おすすめ参考書