トリガの作成

広告

トリガを作成するにはCREATE TRIGGER文を使います。基本的な書式は次の通りです。 

CREATE TRIGGER trigger_name {BEFORE | AFTER} {INSERT | UPDATE | DELETE}
 ON tbl_name FOR EACH ROW trigger_stmt

トリガにはINSERT、UPDATE、DELETEの3種類のトリガが用意されています。

INSERTトリガはテーブルに行が挿入される時に設定したSQL文が実行されます。例えばINSERT文やREPLACE文などです。UPDATEトリガは行が修正される時にSQL文が実行されます。例えばUPDATE文などです。DELETEトリガは行が削除される時にSQL文が実行されます。例えばDELETE文やREPLACE文などです。

またいつ設定したSQL文が実行されるのかを指定することができます。例えばINSERTトリガにBEFOREを指定した場合、行が挿入される前に設定したSQL文が実行されます。同じようにAFTERを指定した場合は行が挿入された後に設定したSQL文が実行されます。

ON句の後にはトリガの対象となるテーブル名を指定します。

例えばINSERTトリガでBEFOREを付ける場合は次のようになります。 

CREATE TRIGGER trigger1 BEFORE INSERT ON table1 FOR EACH ROW trigger_stmt

例えばUPDATEトリガでAFTERを付ける場合は次のようになります。 

CREATE TRIGGER trigger2 AFTER UPDATE ON table2 FOR EACH ROW trigger_stmt

続いて実行するSQL文を記述していきます。

実行されるSQL文

1つのテーブルに対して同じ種類のトリガを複数作成することはできません。その代わり一つのトリガで複数のSQL文を実行することができます。記述するにはBEGIN ... END文を使って次のように記述します。 

DELIMITER $$

CREATE TRIGGER trigger_name {BEFORE | AFTER} {INSERT | UPDATE | DELETE}
 ON tbl_name FOR EACH ROW
 BEGIN
   実行するSQL文1;
   実行するSQL文2;
   ...
 END;
$$

DELIMITER ;

複数の文を記述する場合、各文の最後にセミコロン(;)を記述しなければいけません。ただセミコロンはデフォルトで文の区切りを表すためセミコロンを記述するとCREATE TRIGGER文全体の区切りと判断されてしまいます。

そこでDELIMITERを使って文の区切りを表す文字を別の文字列(上記では$$)に設定してからCREATE TRIGGER文を実行しています。この結果、CREATE TRIGGER文は$$が現れるまでが1つの文として処理され、文の途中でセミコロンを使用することができます。なお文の区切りをセミコロンに戻しておくことを忘れないで下さい。

例えば次のように記述します。 

DELIMITER $$

CREATE TRIGGER trigger1 AFTER DELETE ON table1 FOR EACH ROW
 BEGIN
   DELETE FROM table2 ... ;
   INSERT INTO table3 ... ;
 END;
$$

DELIMITER ;

FOR EACH ROW

MySQLのトリガではFOR EACH ROWのみサポートされています。

この場合、対象テーブルに対しデータが1行処理される度にトリガが1回実行されることを意味します。例えばDELETEトリガが設定されているテーブルに対してDELETE文で5つのデータを削除すると、データが1行削除される毎にトリガが1回実行され合計5回実行されることを意味します。(MySQLでは指定できませんがFOR EACH STATEMENTが指定されるとデータが何行削除されようと一度のDELETE文では1回だけトリガが実行されます)。

サンプル

ここではトリガの動作を確認するための簡単なサンプルを試してみます。次のような2つのテーブルを作成します。 

mysql> create table user(id int, name varchar(20));

mysql> create table log(logtext varchar(20), t timestamp);

p1-1

まずINSERTトリガを1つ作成します。 

mysql> delimiter $$

mysql> create trigger trigger_insert after insert on user for each row
    -> begin
    -> insert into log (logtext) values ('INSERT');
    -> end;
    -> $$

mysql> delimiter ;

p1-2

続いてDELETEトリガを1つ作成します。 

mysql> delimiter $$

mysql> create trigger trigger_delete after delete on user for each row
    -> begin
    -> insert into log (logtext) values ('DELETE');
    -> end;
    -> $$

mysql> delimiter ;

p1-3

では「user」テーブルにデータを追加します。 

mysql> insert into user values(1, '星野');

p1-4

「user」テーブルにはINSERTトリガが作成されていますので「user」テーブルにデータを追加すると「log」テーブルにも自動的にデータが追加されます。では「log」テーブルを確認してみます。

p1-5

このように「log」テーブルにデータが追加されていることが確認できます。

さらに「user」テーブルにデータを2件追加します。

p1-6

「log」テーブルを確認してみます。

p1-7

同じように「log」テーブルにデータが追加されていることが確認できます。

*** ***

「user」テーブルにはDELETEトリガも作成されていますので「user」テーブルからデータを削除すると「log」テーブルにも自動的にデータが追加されます。では「user」テーブルからデータを2つ削除してみます。 

mysql> delete from user where id >= 3;

p1-8

「log」テーブルを確認します。

p1-9

「log」テーブルにデータが2つ追加されていることが確認できます。DELETE文は一度しか実行されていませんが、DELETE文によって2つのデータが削除されているため2回トリガが実行されています。

( Written by Tatsuo Ikura )