PostgreSQL

PostgreSQL トリガーサンプル

トリガーとして動作させるファンクションを事前に作成(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()
;
トリガーの動作確認例
以下2つのテーブルとデータを事前に用意する
-- トリガーを作成する対象テーブルの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=>

トリガー一覧、トリガーの有効化/無効化

スポンサーリンク
コピペで使う