トリガーの作成

広告

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

1.トリガーの作成
2.BEFOREとAFTER
3.FOR EACH ROWとFOR EACH STATEMENT

トリガーの作成

トリガーを作成する場合の書式は次の通りです。 

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文を実行するのかを記述します。

特定のカラムに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);

p1-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;

p1-2

それではデータを追加したり削除したりしてトリガーが動いているのかどうか確認してみます。データをproductテーブルに追加した後でlogテーブルを確認してみます。

p1-3

次に先ほど追加したデータを更新し、その後でlogテーブルを確認してみます。

p1-4

最後に追加したデータを削除し、その後でlogテーブルを確認してみます。

p1-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 トリガー名 BEFORE INSERT ON テーブル名
 BEGIN
  SQL文1;
  SQL文2;
  ...
 END;

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

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 )