トリガーを作成する

特定のテーブルのデータが更新されたら、同時に別のテーブルのデータを更新しておきたい場合があります。このような場合に使用されるのがトリガーです。ここでは SQLite におけるトリガーの作成方法について解説します。

(Last modified: )

トリガーを作成する

トリガーを作成することで、指定した SQL 文が実行されたときに別の SQL 文を自動で実行することができます。トリガーを作成する場合の書式は次の通りです。 

CREATE TRIGGER トリガー名 [ BEFORE | AFTER | INSTEAD OF]
 { DELETE | UPDATE [OF カラム名, ...] | INSERT } ON テーブル名
 [ FOR EACH ROW | FOR EACH STATEMENT ]
 [ WHERE 条件式 ]
 BEGIN
  SQL文1;
  SQL文2;
  ...
 END;

複雑に見えますが、何がきっかけでトリガーが起動するのかを指定し、どのSQL文を実行するのかを記述します。きっかけとなる SQL 文は UPDATE 文、 INSERT 文、そして DELETE 文があります。

テーブルに対してUPDATEが行われた時にトリガーを設定する場合は次のように記述できます。 

CREATE TRIGGER トリガー名 UPDATE ON テーブル名
 BEGIN
  SQL文1;
  SQL文2;
  ...
 END;

また特定のカラムにUPDATEが行われた時にトリガーを設定することもできます。 

CREATE TRIGGER トリガー名 UPDATE OF カラム名 ON テーブル名
 BEGIN
  SQL文1;
  SQL文2;
  ...
 END;

同じようにテーブルに対してDELETE文が実行された時とINSERT文が実行された時にトリガーを設定する場合はそれぞれ次のようになります。 

CREATE TRIGGER トリガー名 DELETE ON テーブル名
 BEGIN
  SQL文1;
  SQL文2;
  ...
 END;

CREATE TRIGGER トリガー名 INSERT ON テーブル名
 BEGIN
  SQL文1;
  SQL文2;
  ...
 END;

例えばデータが更新された時に起動するトリガーを設定しておき、更新前と更新後のデータを取得してトリガーで実行するSQL文の方で利用することもできます。こちらは次のページで解説します。

-- --

それでは実際にトリガーを作成してみます。次のような2つのテーブルを作成しました。

create table product(id integer, name text, price integer);
create table log(id integer primary key, act text);

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

次に product テーブルを対象に3つのトリガーを作成しました。 product テーブルにデータが追加/削除/更新された時にそれぞれトリガーが起動し、 log テーブルの act カラムにログを記録していきます。

create trigger itrigger insert on product
begin
insert into log(act) values('INSERT Action');
end;

create trigger dtrigger delete on product
begin
insert into log(act) values('DELETE Action');
end;

create trigger utrigger update on product
begin
insert into log(act) values('UPDATE Action');
end;

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

それでは product テーブルへデータを追加したり、データの更新や削除を行ったりしてトリガーが起動するかどうか確認します。最初に product テーブルへデータを追加します。その後で log テーブルを確認してください。

insert into product values(1, 'Book', 1500);

product テーブルへデータが追加すると同時にトリガーが起動して log テーブルにデータを追加しています。

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

次に先程 product テーブルへ追加したデータを一部更新します。そのあとで log テーブルを確認してください。

update product set price = 2500 where id = 1;

product テーブルのデータが更新されると同時にトリガーが起動して log テーブルにデータを追加しています。

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

最後に先程 product テーブルへ追加したデータを削除します。そのあとで log テーブルを確認してください。

delete from product where id = 1;

product テーブルのデータが削除されると同時にトリガーが起動して log テーブルにデータを追加しています。

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

このように作成したトリガーに設定した動作が行われた時に、それぞれ各トリガーで定義した SQL 文が実行されていることが確認できます。

BEFOREトリガーとAFTERトリガー

例えばデータが追加された時に実行されるようなトリガーを作成した場合、まずデータが追加された上でトリガーに記述された SQL 文が実行されますが、 BEFORE キーワードを指定しておくとデータが追加される前にまずトリガーに記述された SQL 文が実行されてからデータの追加が行われます。

書式は次の通りです。トリガー名の後に BEFORE を記述して下さい。下記では INSERT の場合を例に書いていますが、 DELETE でも UPDATE でも同じです。 

CREATE TRIGGER トリガー名 BEFORE INSERT ON テーブル名
 BEGIN
  SQL文1;
  SQL文2;
  ...
 END;

同じように AFTER を記述することもできますが、 AFTER がデフォルトの動作だと思われますので明示的に記述する必要はありません。(ただし本当にそうなのかは確認が取れませんでした)。 

CREATE TRIGGER トリガー名 AFTER INSERT ON テーブル名
 BEGIN
  SQL文1;
  SQL文2;
  ...
 END;

なお具体的な例をあげることができないので申し訳ないのですが、 BEFORE トリガーを利用する場合は注意が必要です。下記に公式サイトに記載されている内容をそのまま転記させて頂きます。できれば AFTER トリガーを使うことをおすすめするということです。

Cautions On The Use Of BEFORE triggers

If a BEFORE UPDATE or BEFORE DELETE trigger modifies or deletes a row that was to have been updated or deleted, then the result of the subsequent update or delete operation is undefined. Furthermore, if a BEFORE trigger modifies or deletes a row, then it is undefined whether or not AFTER triggers that would have otherwise run on those rows will in fact run.

The value of NEW.rowid is undefined in a BEFORE INSERT trigger in which the rowid is not explicitly set to an integer.

Because of the behaviors described above, programmers are encouraged to prefer AFTER triggers over BEFORE triggers.

FOR EACH ROWとFOR EACH STATEMENT

トリガーを作成するときに FOR EACH ROW を指定すると対象テーブルに含まれるデータに対して UPDATE や DELETE が1行行われるたびにトリガーで設定された SQL 文が実行されます。また FOR EACH STATEMENT を指定すると何行のデータに対して処理が実行されてもトリガーで設定されたSQL文は一度しか実行されません。 

CREATE TRIGGER トリガー名 INSERT ON テーブル名 FOR EACH ROW
 BEGIN
  SQL文1;
  SQL文2;
  ...
 END;

ただ、SQLite では現在 FOR EACH ROW しか対応していません。その為 FOR EACH ROW を省略しても FOR EACH ROW が設定されたものとみなされます。

-- --

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

( Written by Tatsuo Ikura )

Profile
profile_img

著者 / TATSUO IKURA

プログラミングや開発環境構築の解説サイトを運営しています。

カテゴリ
検索