トリガーの中で対象のテーブルに追加や更新するデータのカラムの値を参照する(OLD.col_name, NEW.col_name)

作成したトリガーは対象となるテーブルにデータを追加したりデータを更新したりした時に起動しますが、トリガーが起動したときに実行される SQL 文の中で、対象となるテーブルに追加や更新されるデータの値を参照して利用することができます。ここでは MySQL でトリガーの中でテーブルに追加や更新されるデータを参照する方法について解説します。

(Last modified: 2023年01月07日)

トリガーの中で追加や削除されるデータのカラムの値を参照する

トリガーの中で追加や削除されるデータのカラムの値を参照する

トリガーは対象のテーブルにデータの追加や更新といった操作が行われた時に起動しますが、トリガーの中で対象テーブルの更新前のデータや更新後のデータを参照したい場合があります。例えばトリガーの対象のテーブルに追加されたデータを他のテーブルにも同じように追加したい場合や、データを更新した時に別のテーブルのデータも同時に更新したい場合などです。

具体的にはトリガーが起動したときに実行されるSQL文において、 OLD.カラム名と NEW.カラム名を使って参照することができます。トリガーの操作が INSERT の場合は NEW.カラム名が使用でき新しく追加されたデータの値を参照できます。操作が DELETE の場合は OLD.カラム名が使用でき削除されたデータの値を参照できます。操作が UPDATE の場合は OLD.カラム名と NEW.カラム名の両方が使用でき更新される前の値と更新された後の値を参照できます。カラム名は対象のテーブルの中に存在するカラム名を指定してください。

では実際に試してみます。トリガーの対象となるテーブルを次のように作成しました。

create table saleslist(name varchar(10), sales int);

トリガーの中で追加や削除されるデータのカラムの値を参照する(1)

今回作成するトリガーは、 saleslist テーブルに新しいデータが追加されたら追加されたデータの name カラムの値を取得し、在庫データを管理している別の stock テーブルの該当データの値を読み出し -1 して格納します。 stock テーブルを次のように作成しデータを追加しました。

create table stock(name varchar(10), stockcount int);

insert into stock values('Mouse', 10);
insert into stock values('Keyboard', 8);

トリガーの中で追加や削除されるデータのカラムの値を参照する(2)

現在このテーブルには次のようにデータが格納されています。

select * from stock;

トリガーの中で追加や削除されるデータのカラムの値を参照する(3)

次にトリガーを作成します。 NEW.name を使用してデータが追加された時に追加したデータの name カラムの値を読み取ります。読み取った値を利用して stock テーブルの該当データを取り出し -1 してからデータを更新します。

delimiter //

create trigger insert_trigger after insert on saleslist for each row
begin
  select stockcount into @stock from stock where name=new.name;
  set @stock = @stock - 1;
  update stock set stockcount=@stock where name=new.name;
end;//

delimiter ;

トリガーの中で追加や削除されるデータのカラムの値を参照する(4)