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つ目は定義した変数に値を入れる方法。
-------------------------------------------------------- --★ 情報の取得方法その1(普通の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.{カラム名}で。
-------------------------------------------------------- --★ 情報の取得方法その2(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とかの使い方が難しいし、面倒だった。
まぁ、こんだけ書いておけば、次回はさほどオロオロすることもないだろう。