更新前と更新後のデータの値をトリガーから参照する

トリガーはデータが追加された時や更新された時に起動し指定した 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);

DELETE文の場合のサンプル(1)

それぞれのテーブルにデータを格納してあります。

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

DELETE文の場合のサンプル(2)

続いてトリガーを作成します。 user テーブルからデータが削除されたら、その顧客に関するデータを history テーブルから削除するトリガーを作成します。

create trigger deleteuser delete on user
begin
delete from history where userid = old.id;
end;

DELETE文の場合のサンプル(3)

これで準備は完了です。それでは user テーブルから顧客を1人削除してみます。その後で history テーブルを確認してみると、その顧客に関する履歴が削除されていることが確認できます。

delete from user where id = 2;

DELETE文の場合のサンプル(4)

UPDATE文の場合のサンプル

次に UPDATE に対するトリガーの場合を試してみます。次のような2つのテーブルを作成しました。 product テーブルは商品の管理用テーブルです。 history テーブルは購入履歴のテーブルです。

create table product(id integer, name text);
create table history(user text, name text, sales integer);

UPDATE文の場合のサンプル(1)

それぞれのテーブルにデータを格納してあります。

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

UPDATE文の場合のサンプル(2)

続いてトリガーを作成します。 product テーブルの商品名が更新されたら、その顧客に関するデータを history テーブルにある同じ商品名もあわせて更新するトリガーを作成します。

create trigger updateproduct update of name on product
begin
update history set name = new.name where name = old.name;
end;

UPDATE文の場合のサンプル(3)

これで準備は完了です。それでは product テーブルの商品名を1つ更新してみます。その後で history テーブルを確認してみると、更新した商品名が含まれる履歴も新しい名前に更新されていることが確認できます。

update product set name = 'iPhone X' where name = 'iPhone';

UPDATE文の場合のサンプル(4)

今回は DELETE 及び UPDATE の場合で試してみましたが、 INSERT の場合も同じようにトリガーで実行する SQL 文の中でトリガーの対象のテーブルに含まれるカラムの値を参照することが可能です。

----

トリガーの中で新しく追加されたデータの値や更新されたデータの更新前と更新後の値を利用する方法について解説しました。

( Written by Tatsuo Ikura )

Profile
profile_img

著者 / TATSUO IKURA

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

カテゴリ
検索