10年前にOracleで作成して以来、久しぶりにトリガーを作成してみた。
しかも、PostgreSQLで。
いろいろできなかったことがあったので調べてみたが、結局わからないところは代替案（要するに適当）で作った。
トリガー作成なんて、今後もあまりやらないだろうから、覚書は細かく書いておくことに。
まず、Functionを作ってからTriggerを作成。
--------------------------------------------------------
-- 走らせる処理はこっちに書く(言語はplpgsql)
--------------------------------------------------------
CREATE OR REPLACE FUNCTION SET_XX_FUNC()
RETURNS trigger AS $$
DECLARE
-- 変数定義 --
BEGIN
-- 処理 --
END;
$$ LANGUAGE ’plpgsql’;
--------------------------------------------------------
-- {table}に設定したテーブルに行が挿入、または行更新された後に処理を走らせる
--------------------------------------------------------
CREATE OR REPLACE TRIGGER SET_XX
AFTER INSERT OR UPDATE ON {table} FOR EACH ROW
EXECUTE PROCEDURE SET_XX_FUNC();
さて、ここまでは良い。
こっからだ。
-------------------------------------------------------- --★ 変数の設定 -------------------------------------------------------- DECLARE item_a int; DECLARE item_b int ：=999 ; item_a := 1;
＞＞1つ目は変数のみ定義。
＞＞2つ目は変数定義と同時に初期値を設定する方法。
＞＞3つ目は定義した変数に値を入れる方法。
--------------------------------------------------------
--★ 情報の取得方法その１（普通のSELECT文）
--------------------------------------------------------
DECLARE item_a int;
DECLARE item_b int;
DECLARE item_c int;
-- (中略) --
SELECT a,b,c INTO item_a,item_b,item_c
FROM {table} WHERE key1=NEW.key1 ;
＞＞SELECT文で取得したデータを変数に入れる場合の書き方はこんな感じ。
SELECT a INTO item_a, b INTO item_b … ってな書き方はNG。
＞＞トリガーで更新・挿入したデータの情報を取得する場合は、NEW.{カラム名}で。
-------------------------------------------------------- --★ 情報の取得方法その２（Executeを利用したSELECT文） -------------------------------------------------------- DECLARE add_months text = ’2months’; DECLARE next_kigen text = ’’; -- (中略) -- EXECUTE ’SELECT TO_CHAR( DATE_TRUNC(’’month’’ , CURRENT_TIMESTAMP || ’ + interval ’ || quote_literal(add_months) || ’)’ || ’ + interval ’’-1days’’,’’YYYYMMDD’’)’ INTO next_kigen;
＞＞普通のSELECT文ではどうしても無理なものは、文字列のSELECT文を実行することになる。
上の例は、現在日時から翌月末の日付(+2ヶ月-1日)を取得する方法。
add_monthsがころころ変わる場合（3か月後、4か月後と）、普通のSELECT文ではどうしてもできなかった。
あとは、サブクエリ―を使う場合もSELECT文では書けないと思っておいたほうがよい。
（テーブル別名＝エイリアスが使えないので。）
なので、文字列を生成して
EXECUTE 文字列 INTO 変数名;
で検索結果をINTOの後ろの変数に格納させる。
文字列内のシングルクォーテーションは’’と、2連続にすること。
＞＞quote_literal関数はよくわからないが、変数の型にあったquateを設定すると思う。
文字列の間で変数を使いたいときは、必ずこの関数を使う。（使わんかったら動かんかったし）
ちなみに、DATE_TRUNC(‘month’,{date_type})は月初を求めるときの関数。
--------------------------------------------------------
--★ 更新後の{item1}が0以下なら処理をやめる（条件文の例）
--------------------------------------------------------
IF NEW.{item1} <= 0 THEN
RETURN NEW;
END IF;
＞＞途中で処理を抜ける処理の書き方がイマイチわからなかったので、とりあえずRETURN NEWしてる。
（正しい方法を教えて！！）
-------------------------------------------------------- --★ 検索結果がないときの判定 -------------------------------------------------------- IF NOT FOUND THEN RETURN NEW; END IF;
＞＞なのだが、これってカーソル処理やFOR文でしか作動しないように思う。
——————————————————–
–★ OLDとNEW
——————————————————–
OLDは更新前のレコードで、NEWは更新後のレコード。
NEW.{カラム名} という感じで使う。
なので、DELETEのトリガーの場合はOLDしかないし、INSERTの場合はNEWしかない。
今回は、AFTER INSERT OR UPDATE ～つまり更新・挿入後に行う～の処理なので、NEWしか使っていない。
衰えた脳みそをフル活用させて1日がかりで作ったのだが、うまく動かなかった ・・・・
で、デバッグ。
本当はログ出力して経過をみたかったのだけれど、ログ出力の仕方がよくわからないし、テスト環境と本番環境で異なる設定をしてもなぁ・・・と思って、動作途中で強制エラーを発生させることにした。
-------------------------------------------------------- --★ RAISE EXCEPTION（強制的に例外を発生させる） -------------------------------------------------------- DECLARE add_months text = ’2months’; -- (中略) RAISE EXCEPTION '% ここまで終わったぜ', add_months;
＞＞エラーメッセージに「2months ここまで終わったぜ」と表示される。
（%は可変値で、2つ目以降の引数で設定する。Cとかの%dとかと同じ）
なので、とりあえず、ここまでは走ってるよなぁ・・・ということで、
次はこの例外発生処理を後ろに移動させてから実行する。
これを何度か繰り返して、なんとか思い通りに動作するところまで行った。
とりあえず、直近で必要な情報はこんなところ。
今回はカーソルとかは使わなかったので、使うときはまた追記なり投稿なりしようと思う。
EXECUTEとかの使い方が難しいし、面倒だった。
まぁ、こんだけ書いておけば、次回はさほどオロオロすることもないだろう。