トリガー使ったよ！

Tips postgres

トリガーとは？

トリガーは、表に対してINSERT、UPDATE、DELETEなどの変更処理が加えられたときに、
それを契機に自動的に実行される特殊なストアドプロシージャのことです。


トリガーを定義するときには、対象となるテーブル、起動契機となるテーブルに対する変更処理、
トリガーの処理内容、トリガーの起動するタイミングなどを指定します。
トリガーは指定したテーブルを監視し、指定した変更処理がテーブルに対して行われると、
指定したタイミングで指定した処理を実行します。

Postgresにおけるトリガーの基本構文

CREATE TRIGGER トリガー名
         { BEFORE | AFTER }
         { INSERT | UPDATE [OF 列名,...] | DELETE }
           [ OR { INSERT | UPDATE [OF 列名,...] | DELETE}]
           [ ... ]
         ON テーブル名
         FOR EACH { ROW | STATEMENT }
         [ WHEN 条件式 ]
         EXECUTE PROCEDURE 関数名(引数) ;
  • { BEFORE | AFTER } ・・・トリガーを変更処理の前に実行するのか、後に実行するのかを指定する。
  • FOR EACH { ROW | STATEMENT }・・・トリガーを行ごとに起動するときは FOR EACH ROW、一度だけしか起動しない場合はFOR EACH STATEMENTとする。
  • EXECUTE PROCEDURE 関数名(引数)・・・PostgreSQL の場合は予め定義しておいた関数名を EXECUTE PROCEDURE に続けて記述する。Oracleなどは起動契機と処理を合わせて定義するらしい。

実験

t_userというテーブルが登録、更新された場合に、t_user.idを0でパディングした値で、
user_idを更新するというもの。

関数の定義

CREATE OR REPLACE FUNCTION griffin.upd_user_id()
RETURNS "trigger" AS
  begin
  if new.user_id is null then
    update t_user set user_id = lpad(new.id, 4, '0') where id = new.id;
  else
    if lpad(new.id, 4, '0') <> new.user_id then
      update t_user set user_id = lpad(new.id, 4, '0') where id = new.id;
    end if;
  end if;
  return new;
end;
LANGUAGE 'plpgsql' VOLATILE;
  • トリガーで使用する関数を定義する場合、戻り値は"trigger"となるらしい。
  • INSERT、UPDATEを起動契機としている為、トリガーによりUPDATEが走った時もトリガーが

呼び出されてしまう。えぇ、無限ループにはまりました。。ださっ！


トリガーの定義

CREATE TRIGGER upd_user_id_trig
  AFTER INSERT OR UPDATE
  ON griffin.t_user
  FOR EACH ROW
  EXECUTE PROCEDURE griffin.upd_user_id();