更新前と更新後のデータの値をトリガーから参照する
トリガーはデータが追加された時や更新された時に起動し指定した SQL 文を実行しますが、新しく追加されたデータの値や更新されたデータの更新前と更新後の値をトリガーの中で実行される SQL 文の中ら参照することができます。ここでは SQLite の環境で更新前と更新後のデータの値をトリガーから参照する方法について解説します。
(Last modified: )
NEW.カラム名とOLD.カラム名
新しく追加されたデータの値や更新されたデータの更新前と更新後の値をどのような場合に使用するのかというと、テーブルに含まれるデータの値を更新した時に、他のテーブルに含まれる同じ値も同時に更新したい場合などに利用できます。
カラムの値を参照したい場合はトリガーの中で実行される SQL 文の中では NEW.カラム名 と OLD.カラム名 を使います。
NEW.カラム名
OLD.カラム名
NEW.カラム名 は INSERT 文または UPDATE 文でトリガーが起動した場合に利用できます。OLD.カラム名 は DELETE 文または UPDATE 文でトリガーが起動した場合に利用できます。カラム名は INSERT 文や DELETE 文の対象となっているテーブルのカラムである必要があります。
簡単な例で説明してみます。 user テーブルに新しいデータ追加された時を対象としたトリガーを作成します。トリガーが起動すると、新しく追加されたデータの name カラムの値を参照し別の log テーブルに追加する SQL 文を実行しています。追加されたデータの name カラムの値は new.name と記述することで参照することができます。
create trigger mytrigger insert on user
begin
insert into log values(new.name);
end;
このようにトリガーの中で実行される SQL 文の中で、トリガーの対象となっているテーブルに追加されたり削除されたりしたデータのカラムの値を参照することができます。
DELETE文の場合のサンプル
DELETE に対するトリガーの場合を試してみます。次のような2つのテーブルを作成しました。 user テーブルは顧客情報の管理用テーブルです。 history テーブルは購入履歴のテーブルです。
create table user(id integer, name text);
create table history(userid integer, goods text, sales integer);
それぞれのテーブルにデータを格納してあります。
insert into user values(1, 'Honda');
insert into user values(2, 'Suzuki');
insert into user values(3, 'Yamada');
insert into history values(1, 'PC', 150000);
insert into history values(2, 'Mouse', 3400);
insert into history values(1, 'Watch', 8500);
insert into history values(3, 'Light', 2400);
insert into history values(2, 'Mobile', 32000);
続いてトリガーを作成します。 user テーブルからデータが削除されたら、その顧客に関するデータを history テーブルから削除するトリガーを作成します。
create trigger deleteuser delete on user
begin
delete from history where userid = old.id;
end;
これで準備は完了です。それでは user テーブルから顧客を1人削除してみます。その後で history テーブルを確認してみると、その顧客に関する履歴が削除されていることが確認できます。
delete from user where id = 2;
UPDATE文の場合のサンプル
次に UPDATE に対するトリガーの場合を試してみます。次のような2つのテーブルを作成しました。 product テーブルは商品の管理用テーブルです。 history テーブルは購入履歴のテーブルです。
create table product(id integer, name text);
create table history(user text, name text, sales integer);
それぞれのテーブルにデータを格納してあります。
insert into product values(1, 'Android');
insert into product values(2, 'iPhone');
insert into product values(3, 'iPad');
insert into history values('Honda', 'iPhone', 30000);
insert into history values('Mori', 'iPad', 45000);
insert into history values('Oda', 'Android', 39000);
insert into history values('Takagi', 'iPhone', 31000);
続いてトリガーを作成します。 product テーブルの商品名が更新されたら、その顧客に関するデータを history テーブルにある同じ商品名もあわせて更新するトリガーを作成します。
create trigger updateproduct update of name on product
begin
update history set name = new.name where name = old.name;
end;
これで準備は完了です。それでは product テーブルの商品名を1つ更新してみます。その後で history テーブルを確認してみると、更新した商品名が含まれる履歴も新しい名前に更新されていることが確認できます。
update product set name = 'iPhone X' where name = 'iPhone';
今回は DELETE 及び UPDATE の場合で試してみましたが、 INSERT の場合も同じようにトリガーで実行する SQL 文の中でトリガーの対象のテーブルに含まれるカラムの値を参照することが可能です。
----
トリガーの中で新しく追加されたデータの値や更新されたデータの更新前と更新後の値を利用する方法について解説しました。
( Written by Tatsuo Ikura )
著者 / TATSUO IKURA
これから IT 関連の知識を学ばれる方を対象に、色々な言語でのプログラミング方法や関連する技術、開発環境構築などに関する解説サイトを運営しています。