トリガーとして動作させるファンクションを事前に作成(create function)し、次にそのファンクションをテーブルへ関連づける事(create trigger)でトリガーの定義が完成します。
トリガーとして動作させるファンクションを作成する
一般的なファンクションと同じように作成しますが、通常ファンクションと異なるのは「returns trigger」とする点です。以下のファンクションは、kudamonoテーブルに対し発行された更新内容をlogging_kudamonoテーブルへ保存していくものです。
CREATE OR REPLACE FUNCTION sooni.trg_kudamono()
RETURNS trigger
LANGUAGE 'plpgsql'
AS $BODY$
BEGIN
IF (TG_OP = 'DELETE') THEN
INSERT INTO logging_kudamono(event,txid,item_no,item_name,size,price,note)
SELECT 'DELETE',txid_current(),OLD.item_no,OLD.item_name,OLD.size,OLD.price,OLD.note;
RETURN OLD;
ELSIF (TG_OP = 'UPDATE') THEN
INSERT INTO logging_kudamono(event,txid,item_no,item_name,size,price,note)
SELECT 'UPDATE OLD',txid_current(),OLD.item_no,OLD.item_name,OLD.size,OLD.price,OLD.note;
INSERT INTO logging_kudamono(event,txid,item_no,item_name,size,price,note)
SELECT 'UPDATE NEW',txid_current(),NEW.item_no,NEW.item_name,NEW.size,NEW.price,NEW.note;
RETURN NEW;
ELSIF (TG_OP = 'INSERT') THEN
INSERT INTO logging_kudamono(event,txid,item_no,item_name,size,price,note)
SELECT 'INSERT',txid_current(),NEW.item_no,NEW.item_name,NEW.size,NEW.price,NEW.note;
RETURN NEW;
END IF;
RETURN NULL;
END;
$BODY$;
トリガーをテーブルへ関連付ける
上記作成したsooni.trg_kudamono()ファンクションをsooni.kudamonoテーブルへ関連づけて「kudamono_trigger」という名前で定義する。
create or replace trigger kudamono_trigger before insert or delete or update on sooni.kudamono for each row execute procedure sooni.trg_kudamono() ;
トリガーの動作確認例
以下２つのテーブルとデータを事前に用意する
-- トリガーを作成する対象テーブルのcreate
--
create table kudamono (
item_no integer
,item_name character varying(20)
,size character varying(30)
,price integer
,note character varying(50)
);
-- トリガーからinsertするテーブルのcreate
--
create table logging_kudamono (
rowid serial
,txid bigint
,item_no integer
,item_name character varying(20)
,size character varying(30)
,price integer
,note character varying(50)
);
-- 初期データのinsert
-- （この時点ではまだトリガーは設定していない）
--
INSERT INTO kudamono(item_no,item_name,size,price,note) values
(10,'バナナ','400g',240,'まだ青み残ってる')
,(20,'林檎','つがるS',140,'甘味強い')
,(30,'みかん','せとか',120,'少し小ぶり')
;上記作成したテーブルに対しトリガーを設定し動作を確認する
-- トリガーの定義
--
myposdb=> create or replace trigger kudamono_trigger before insert or delete or update on sooni.kudamono
myposdb-> for each row execute procedure sooni.trg_kudamono()
myposdb-> ;
CREATE TRIGGER
myposdb=> select * from logging_kudamono t order by rowid
myposdb-> ;
rowid | event | txid | item_no | item_name | size | price | note
-------+-------+------+---------+-----------+------+-------+------
(0 行)
--
-- トリガーを関連付けたテーブルに対しUPDATEを発行
--
myposdb=> update kudamono set item_name='リンゴ' where item_no = 20
myposdb-> ;
UPDATE 1
--
-- トリガーの動作を確認
-- update 前のレコードと、後のレコードが取得されている事が確認できる。
--
myposdb=> select * from logging_kudamono t order by rowid;
rowid | event | txid | item_no | item_name | size | price | note
-------+------------+------+---------+-----------+---------+-------+----------
1 | UPDATE OLD | 2303 | 20 | 林檎 | つがるS | 140 | 甘味強い
2 | UPDATE NEW | 2303 | 20 | リンゴ | つがるS | 140 | 甘味強い
(2 行)
--
-- 次にinsertを発行
--
myposdb=> INSERT INTO kudamono(item_no,item_name,size,price,note) values (500,'ナシ','二十世紀300g',210,'鳥取県産');
INSERT 0 1
--
-- insertしたレコードが確認できる
--
myposdb=> select * from logging_kudamono t order by rowid;
rowid | event | txid | item_no | item_name | size | price | note
-------+------------+------+---------+-----------+--------------+-------+----------
1 | UPDATE OLD | 2303 | 20 | 林檎 | つがるS | 140 | 甘味強い
2 | UPDATE NEW | 2303 | 20 | リンゴ | つがるS | 140 | 甘味強い
3 | INSERT | 2304 | 500 | ナシ | 二十世紀300g | 210 | 鳥取県産
(3 行)
myposdb=> select * from kudamono order by item_no
myposdb-> ;
item_no | item_name | size | price | note
---------+-----------+--------------+-------+------------------
10 | バナナ | 400g | 240 | まだ青み残ってる
20 | リンゴ | つがるS | 140 | 甘味強い
30 | みかん | せとか | 120 | 少し小ぶり
500 | ナシ | 二十世紀300g | 210 | 鳥取県産
(4 行)
myposdb=>
