【PostgreSQL】トリガーで履歴テーブルへデータをinsertする

2021年5月17日

トリガーで履歴テーブルへデータをinsert

最新価格を保持するテーブルpriceと価格履歴テーブルprice_historyを考えます。priceテーブルに対する新規データ登録、データ更新時にprice_historyテーブルに同じデータをinsertすることで価格履歴を保持します。

price_historyテーブルへのデータ登録を確実に行うためにトリガーを使ってinsertを行います。トリガーはpriceテーブルへinsert、updateを行ったときに実行するようにします。

テーブルとトリガーの作成

以下のようにテーブルとトリガーを作成します。

-- 最新価格を保持するテーブル
create table price (
    id serial,
    product_id int not null,
    price numeric not null,
    updated_at timestamp with time zone not null,
    constraint pk_price primary key(id)
);

-- 価格履歴を保持するテーブル
create table price_history (
    id serial,
    product_id int not null,
    price numeric not null,
    updated_at timestamp with time zone not null,
    constraint pk_price_history primary key(id)
);

-- 更新日時をセットする関数
create function set_updated_at() returns trigger as $set_updated_at$
begin
    NEW.updated_at = now();
    return NEW;
end;
$set_updated_at$
language plpgsql;

-- 価格履歴をinsertする関数
create function insert_price_history() returns trigger as $insert_price_history$
begin
    insert into price_history (product_id, price, updated_at) values (NEW.product_id, NEW.price, NEW.updated_at);
    return NEW;
end;
$insert_price_history$
language plpgsql;

-- 更新日時をセットするトリガーを作成
create trigger set_updated_at_trigger before insert or update on price for each row execute function set_updated_at();
create trigger set_updated_at_trigger before insert or update on price_history for each row execute function set_updated_at();

-- 価格履歴をinsertするトリガーを作成
create trigger insert_price_history_trigger after insert or update on price for each row execute function insert_price_history();

動作確認

priceテーブルに対してinsert、updateを行い動作確認を行います。

-- priceテーブルへのinsert
$ insert into price (product_id, price) values (1, 100);
INSERT 0 1

$ select * from price;
 id | product_id | price |          updated_at
----+------------+-------+-------------------------------
  1 |          1 |   100 | 2021-05-17 14:36:17.174571+00
(1 row)

$ select * from price_history;
 id | product_id | price |          updated_at
----+------------+-------+-------------------------------
  1 |          1 |   100 | 2021-05-17 14:36:17.174571+00

$ insert into price (product_id, price) values (2, 200);
INSERT 0 1

$ select * from price;
 id | product_id | price |          updated_at
----+------------+-------+-------------------------------
  1 |          1 |   100 | 2021-05-17 14:36:17.174571+00
  2 |          2 |   200 | 2021-05-17 14:37:02.012947+00

$ select * from price_history;
 id | product_id | price |          updated_at
----+------------+-------+-------------------------------
  1 |          1 |   100 | 2021-05-17 14:36:17.174571+00
  2 |          2 |   200 | 2021-05-17 14:37:02.012947+00

$ update price set price = 201 where id = 1;
UPDATE 1

$ select * from price;
 id | product_id | price |          updated_at
----+------------+-------+-------------------------------
  2 |          2 |   200 | 2021-05-17 14:37:02.012947+00
  1 |          1 |   201 | 2021-05-17 14:37:48.462558+00
(2 rows)

$ select * from price_history;
 id | product_id | price |          updated_at
----+------------+-------+-------------------------------
  1 |          1 |   100 | 2021-05-17 14:36:17.174571+00
  2 |          2 |   200 | 2021-05-17 14:37:02.012947+00
  3 |          1 |   201 | 2021-05-17 14:37:48.462558+00