Menu

PostgreSQL トリガーの使い方:CREATE TRIGGER の例

トリガーは、行の挿入、更新、削除のときに関数を自動で実行します。PostgreSQL では trigger を返すトリガー関数を書き、CREATE TRIGGER でテーブルに割り当てます。

このページのコードはエディタで実行できます。編集してすぐに結果を確認できます。

トリガーを使うと、テーブルが変わったときに 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 の文レベルトリガーを作ります。

Coddyのプログラミング言語のイラスト

Coddyでコードを学ぼう

始める