トリガーを使うと、テーブルが変わったときに PostgreSQL が関数を自動で呼び出します。先に RETURNS trigger で宣言したトリガー関数を書き、それを割り当てます: CREATE TRIGGER name BEFORE UPDATE ON table FOR EACH ROW EXECUTE FUNCTION fn();。定番の使い方は、updated_at 列を最新に保つことです。
更新されたのは投稿 1 だけなので、タイムスタンプを持つのも投稿 1 だけです。NEW はこれから書き込まれる行で、BEFORE トリガーはそれを変更でき、返す必要があります。同じ関数を、updated_at 列を持つすべてのテーブルに割り当てられます。
BEFORE、AFTER、INSTEAD OF の違い
| タイミング | 実行される時点 | よくある使い方 |
|---|---|---|
BEFORE | 行が書き込まれる前 | NEW の変更や検証、行のスキップ |
AFTER | 行が書き込まれた後 | 他のテーブルへの書き込み: 監査ログ、カウンター |
INSTEAD OF | 変更の代わりに (ビューのみ) | ビューを書き込み可能にする |
FOR EACH ROW は影響を受けた行ごとに1回関数を実行します。FOR EACH STATEMENT (デフォルト) は、行が1つも変わらなくても文ごとに1回実行します。行トリガーでは、NEW が新しい行 (INSERT、UPDATE)、OLD が以前の行 (UPDATE、DELETE) を持ちます。TG_OP は起動した操作の名前、TG_TABLE_NAME はテーブル名です。
BEFORE 行トリガーの戻り値で、その後の動きが決まります。そのまま進めるなら NEW (変更の有無を問わず) を返し、エラーなしで行をスキップするなら NULL を返します。AFTER トリガーの戻り値は無視されるので、慣例として NULL を返します。
AFTER トリガーで監査ログを書く
監査テーブルには、2件の INSERT、10 から 12 への UPDATE、価格 20 の商品の DELETE が記録されます。挿入では OLD が、削除では NEW が NULL なので coalesce を使っています。行全体を保存するなら、to_jsonb(OLD) と to_jsonb(NEW) を jsonb 列に入れます。
変更を拒否する、またはスキップする
BEFORE トリガーは、RAISE EXCEPTION で変更を拒否して文を中断することも、NULL を返して行をスキップすることもできます。
DELETE は下書きを削除し、支払い済みの請求書は何も言わずに残しました。BEFORE DELETE トリガーは、削除を進めるときに OLD を返します。代わりに文全体を失敗させるには、RETURN NULL を RAISE EXCEPTION 'invoice % is paid', OLD.id; に置き換えます。黙って残る行より、エラーのほうがデバッグしやすくなります。
列が変わったときだけ実行する
UPDATE OF column は、その列を設定する更新だけにトリガーを限定します。WHEN 条件が false なら関数は呼び出されません。IS DISTINCT FROM は NULL を値として扱うので、NULL への変更や NULL からの変更も捉えます。
1回目の更新は同じ価格を設定するので WHEN は false になり、カウンターは 0 のままです。2回目は価格が変わり、price_changed は最終的に 1 になります。
トリガーを一覧、無効化、削除する
-- triggers you created (excludes the ones behind foreign keys)
SELECT tgrelid::regclass AS table_name, tgname
FROM pg_trigger WHERE NOT tgisinternal;
ALTER TABLE products DISABLE TRIGGER products_price;
ALTER TABLE products ENABLE TRIGGER products_price;
DROP TRIGGER products_price ON products;
psql では \d products でテーブルのトリガーを一覧できます。CREATE OR REPLACE TRIGGER は PostgreSQL 14 から、EXECUTE FUNCTION は PostgreSQL 11 から使えます。古いコードは EXECUTE PROCEDURE と書いていますが、今も動きます。自分のテーブルに書き込むトリガーは自分自身を再び起動しうるので、ループを防ぐ対策をします (pg_trigger_depth() は入れ子の深さを返します)。すべての TG_ 変数は トリガー関数の章 に、PL/pgSQL そのものは 関数とプロシージャ にまとめています。
よくある質問
PostgreSQL のトリガーは関数なしで動かせますか?
できません。CREATE TRIGGER は常に RETURNS trigger で宣言した関数を呼び出します。先に関数を (たいていは PL/pgSQL で) 書き、それから割り当てます。
同じテーブルのトリガーはどの順番で実行されますか?
同じイベントに対する同じ種類のトリガーは、名前のアルファベット順に実行されます。順番が大事なら、名前の先頭に数字や文字を付けます。
トリガーでエラーが起きると文はロールバックされますか?
はい。トリガー内で (たとえば RAISE EXCEPTION で) 発生したエラーは、トリガーを起動した文を中断します。セーブポイントで捕まえない限り、トランザクション全体も中断されます。
TRUNCATE でトリガーは実行されますか?
行レベルの DELETE トリガーは実行されません。TRUNCATE に反応させるには、BEFORE TRUNCATE か AFTER TRUNCATE の文レベルトリガーを作ります。