トリガーを作成する(CREATE TRIGGER文)

MySQL では新しくトリガーを作成するときに CREATE TRIGGER 文を使用します。ここでは MySQL でトリガーを作成する方法について解説します。

(Last modified: )

トリガーを作成する

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

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

トリガーは対象となるテーブル( tbl_name )で指定した操作があった場合に実行する SQL 文( trigger_body のところで記述)を定義しておくものです。トリガーが起動する操作の種類は INSERT 、 UPDATE 、 DELETE の3種類があります。

INSERT トリガーはテーブルに行が追加される前または後に設定した SQL 文が実行されます。例えばテーブルに対して INSERT 文や REPLACE 文、 LOAD DATA 文が実行された時です。

UPDATE トリガーはテーブルのデータが更新される前または後に設定した SQL 文が実行されます。例えばテーブルに対して UPDATE 文が実行された時です。

DELETE トリガーはテーブルのデータが削除される前または後で設定した SQL 文が実行されます。例えばテーブルに対して DELETE 文や REPLACE 文が実行された時です。なお DROP TABLE 文でテーブルを削除した場合は起動しません。

トリガーの種類、そしてトリガーが起動するのは対象となる動作が行われる前なのか後なのか、そしてトリガー起動した時に実行する SQL 文を指定してトリガーを作成します。

MySQL のトリガーでは FOR EACH ROW のみサポートされています。
そのため対象のテーブルで例えば DELETE 文を 1 回実行したときにデータが 5 つ削除されるとデータが 1 つ削除されるごとにトリガーが 1 回起動します。トリガーではDELETE 文 1 回に対して 1 回だけトリガーが起動する FOR EACH STATEMENT を選択できるデータベースもあります。

-- --

それでは実際に試してみます。最初にトリガーの対象となるテーブルを次のように作成しました。

create table saleslist(id int auto_increment primary key, name varchar(10), sales int);

トリガーを作成する(1)

今回はトリガーが起動したらログを記録するためのテーブルに記録を残しておくことにします。トリガーで利用するテーブルを作成しました。

create table log(id int auto_increment primary key, log varchar(20), dt datetime);

トリガーを作成する(2)

次にトリガーを作成します。今回は INSERT 、 UPDATE 、 DELETE のそれぞれでトリガーを作成します。起動するタイミングはすべて AFTER です。

create trigger insert_trigger after insert on saleslist for each row insert into log (log, dt) values('Insert', now());

create trigger update_trigger after update on saleslist for each row insert into log (log, dt) values('Update', now());

create trigger delete_trigger after delete on saleslist for each row insert into log (log, dt) values('Delete', now());

トリガーを作成する(3)

トリガーの作成が完了しました。

-- --

それではトリガーの対象となっている saleslist テーブルにデータを 2 つ追加します。

insert into saleslist (name, sales) values('Table', 27000);
insert into saleslist (name, sales) values('Chair', 15000);

トリガーを作成する(4)

対象のテーブルにデータが INSERT されましたので INSERT トリガーが起動してログ用のテーブルにデータが自動的に追加されます。ではログ用のテーブルからデータを取得してみます。

select * from log;

トリガーを作成する(5)

INSERT トリガーが起動して指定した SQL 文が実行されていることが確認できました。 INSERT 文は 2 回実行されており、それぞれ 1 つのデータを追加しているのでトリガーも 2 回実行されています。

-- --

次にトリガーの対象となっている saleslist テーブルのデータを更新します。

update saleslist set sales=26000 where id=1;

トリガーを作成する(6)

対象のテーブルのデータが UPDATE されましたので UPDATE トリガーが起動してログ用のテーブルにデータが自動的に追加されます。ではログ用のテーブルからデータを取得してみます。

トリガーを作成する(7)

UPDATE トリガーが起動して指定した SQL 文が実行されていることが確認できました。 UPDATE は 1 回実行されており、 1 つのデータに対して更新が行われましたのでトリガーも 1 回実行されています。

-- --

最後にトリガーの対象となっている saleslist テーブルのデータをすべて削除します。

delete from saleslist;

トリガーを作成する(8)

対象のテーブルのデータが DELETE されましたので DELETE トリガーが起動してログ用のテーブルにデータが自動的に追加されます。ではログ用のテーブルからデータを取得してみます。

トリガーを作成する(9)

DELETE トリガーが起動して指定した SQL 文が実行されていることが確認できました。 DELETE が実行されたのは 1 回ですが、削除されたデータは 2 つあるのでトリガーも 2 回実行されています。

同じ操作を対象として同じタイミングで起動するトリガーを複数作成する

トリガーの操作の対象は INSERT 、 UPDATE 、 DELETE の3種類がありますが、同じ操作を対象としてトリガーが起動するタイミングも同じトリガーを複数作成することができます。特に指定しない場合は、作成された順番にトリガーが起動します。

では実際に試してみます。対象となるテーブルを次のように作成しました。

create table saleslist(id int auto_increment primary key, name varchar(10), sales int);

同じ操作を対象として同じタイミングで起動するトリガーを複数作成する(1)

今回はトリガーが起動したらログを記録するためのテーブルに記録を残しておくことにします。トリガーで利用するテーブルを作成しました。

create table log(id int auto_increment primary key, log varchar(20), dt datetime);

同じ操作を対象として同じタイミングで起動するトリガーを複数作成する(2)

次にトリガーを作成します。今回は操作の対象として INSERT 、タイミングは AFTER のトリガーを 2 つ作成します。

create trigger insert_trigger_a after insert on saleslist for each row insert into log (log, dt) values('Insert A', now());

create trigger insert_trigger_b after insert on saleslist for each row insert into log (log, dt) values('Insert B', now());

同じ操作を対象として同じタイミングで起動するトリガーを複数作成する(3)

それではトリガーの対象となっている saleslist テーブルにデータを 1 つ追加します。

insert into saleslist (name, sales) values('Table', 27000);

同じ操作を対象として同じタイミングで起動するトリガーを複数作成する(4)

対象のテーブルにデータが INSERT されましたので INSERT トリガーが起動します。今回は同じ操作を対象として 2 つのトリガーが作成されているので、作成された順にトリガーが起動しています。ログ用のテーブルからデータを取得してみます。

select * from log;

同じ操作を対象として同じタイミングで起動するトリガーを複数作成する(5)

2 つのトリガーがそれぞれ起動していること、そしてトリガーが作成された順に起動されていることが確認できました。

-- --

同じ操作を対象としてトリガーが起動するタイミングも同じトリガーを複数作成した場合に、トリガーが起動する順番を指定することもできます。次の書式を使います。 

CREATE
    TRIGGER trigger_name
    { BEFORE | AFTER } { INSERT | UPDATE | DELETE }
    ON tbl_name FOR EACH ROW
    { FOLLOWS | PRECEDES } other_trigger_name
    trigger_body

other_trigger_name に同じ操作を対象としてトリガーが起動するタイミングも同じトリガーの名前を指定し、指定したトリガーの後に実行したい場合には FOLLOWS 、指定したトリガーの前に実行したい場合には PRECEDES を指定してください。

それでは実際に試してみます。先ほど作成したトリガーの対象のテーブルと、トリガーから利用するログ用のテーブルはそのまま使用し、今回は操作の対象として UPDATE 、タイミングは AFTER のトリガーを 2 つ作成します。そして 2 つ目のトリガーは 1 つ目のトリガーよりも前に実行します。

create trigger update_trigger_a after update on saleslist for each row insert into log (log, dt) values('Update A', now());

create trigger update_trigger_b after update on saleslist for each row precedes update_trigger_a insert into log (log, dt) values('Update B', now());

同じ操作を対象として同じタイミングで起動するトリガーを複数作成する(6)

それではトリガーの対象となっている saleslist テーブルのデータを 1 つ更新します。

update saleslist set sales=26000 where id=1;

同じ操作を対象として同じタイミングで起動するトリガーを複数作成する(7)

対象のテーブルにデータが INSERT されましたので INSERT トリガーが起動します。今回は同じ操作を対象として 2 つのトリガーが作成されています。トリガーが起動する順番は今回は指定しており、最初に後で作成したトリガーで次に最初に作成したトリガーとなります。ログ用のテーブルからデータを取得してみます。

select * from log;

同じ操作を対象として同じタイミングで起動するトリガーを複数作成する(8)

2 つのトリガーがそれぞれ起動していること、そしてトリガーが指定した順番に起動されていることが確認できました。

-- --

CREATA TRIGGER 文でトリガーを作成する方法について解説しました。

( Written by Tatsuo Ikura )

プロフィール画像

著者 / TATSUO IKURA

これから IT 関連の知識を学ばれる方を対象に、色々な言語でのプログラミング方法や関連する技術、開発環境構築などに関する解説サイトを運営しています。