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

広告

トリガーはデータが追加された時や更新された時に起動し指定したSQL文を実行しますが、新しく追加されたデータの値や更新されたデータの更新前と更新後の値をトリガーの中で実行されるSQL文の中ら参照することができます。ここでは更新前と更新後のデータの値をトリガーから参照する方法について解説します。

1.NEW.カラム名とOLD.カラム名
2.DELETE文の場合のサンプル
3.UPDATE文の場合のサンプル

NEW.カラム名とOLD.カラム名

どのような場合に使用するのかというと、テーブルに含まれるデータの値を更新した時に、他のテーブルに含まれる同じ値も同時に更新したい場合などに利用できます。

カラムの値を参照したい場合はトリガーの中で実行されるSQL文の中では次の2つの記述を行うことができます。 

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

p2-1

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

p2-2

今回はuserテーブルから顧客が削除されたら、その顧客に関する購入履歴をhistoryテーブルから削除するトリガーを作成します。 

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

p2-3

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

p2-4

UPDATE文の場合のサンプル

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

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

p2-5

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

p2-6

今回はproductテーブルの商品名が更新されたら、historyテーブルの中にある同じ商品名についても合わせて更新するトリガーを作成します。 

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

p2-7

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

p2-8

----

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

( Written by Tatsuo Ikura )