PostgreSQL

トリガの作り方

滅多に作らないから、時々作るときにいつも困る。 メモを残しておく。

サンプルは、同じ構造を持ち、スキーマの異なる3つのテーブルがあり、ひとつがマスタテーブル。 これが更新されると、残りのふたつのテーブルに反映されるようにする

マスタ:public.users
子1 :rms.users
子2 :stt.users
test=# \d public.users
                               Table "public.users"
 Column |         Type          |                     Modifiers
--------+-----------------------+----------------------------------------------------
id     | integer               | not null default nextval('users_id_seq'::regclass)
userid | character varying(20) |
name   | character varying(20) |
Indexes:
   "users_pkey" PRIMARY KEY, btree (id)
test=# \d rms.users
            Table "rms.users"
Column |         Type          | Modifiers
--------+-----------------------+-----------
id     | integer               | not null
userid | character varying(20) |
name   | character varying(20) |
Indexes:
   "users_pkey" PRIMARY KEY, btree (id)

test=# \d stt.users
            Table "stt.users"
Column |         Type          | Modifiers
--------+-----------------------+-----------
id     | integer               | not null
userid | character varying(20) |
name   | character varying(20) |
Indexes:
   "users_pkey" PRIMARY KEY, btree (id)

まず、関数を作る。

create function update_users() returns opaque
 as '
 begin
  if TG_OP=''INSERT'' then
   insert into rms.users (id, userid, name) values (new.id ,new.userid, new.name);
   insert into stt.users (id, userid, name) values (new.id ,new.userid, new.name);
   return new;
  else
  if TG_OP=''UPDATE'' then
   update rms.users set id=new.id, userid=new.userid, name=new.name
    where id=old.id;
   update stt.users set id=new.id, userid=new.userid, name=new.name
    where id=old.id;
    return new;
  else
  if TG_OP=''DELETE'' then
   delete from rms.users where id=old.id;
   delete from stt.users where id=old.id;
   return null;
  end if;
 end if;
end if;
end;
'
language 'plpgsql';

PL/pgSQL が使えるようになっていないと、下のようなエラーがでる

ERROR:  language "plpgsql" does not exist

その場合は、次のようにして使えるようにする。

create language 'plpgsql';

関数ができたら、トリガを作る。

create trigger trig_users after insert or update or delete on public.users for each row execute procedure update_users();

これを実行すると、トリガはできるけど、次の警告が出た。

WARNING:  changing return type of function update_users from "opaque" to "trigger"

はて。

関数定義の1行目、 returns opaque を return tirgger に変えたら出なくなった。

あとは、public.users テーブルに INSERT, UPDATE, DELETE を試して、子テーブルに反映されることを確認する。

ちなみに、users テーブルに2件データを入れると、当然子テーブルにも2件ずつ INSERT されるが、それぞれの子テーブルでデータを片方ずつ消してから、 users テーブルで update をかけると、子テーブルのうち、該当データだけが更新され、すでに消えてしまったエントリは更新されなかった。 単純に指示どおりの SQL を実行するだけというのが確認できた。

トリガの消し方

関数もトリガも消したいときは、作ったときの逆順でトリガ、関数の順に消す。

drop trigger on public.users;

次に関数

drop function update_users();

簡単。


トップ   編集 凍結 差分 バックアップ 添付 複製 名前変更 リロード   新規 一覧 単語検索 最終更新   ヘルプ   最終更新のRSS