2026年8月5日

PostgreSQLのトリガーとファンクション設定（PL/pgSQLで更新日時・監査ログを自動化）

データベースサーバー

PostgreSQL のトリガーは、「RETURNS trigger のファンクションを PL/pgSQL で作り、CREATE TRIGGER でテーブルのイベントに結び付ける」という2段階で設定します。MySQL のようにトリガーの中に処理を直接書くのではなく、処理はファンクションとして独立させる点が特徴です。この記事では、ファンクションの基本、更新日時を自動で入れるトリガー、変更履歴を残す監査ログのトリガー、BEFORE と AFTER の使い分け、動作確認とトラブルの調べ方までを説明します。

なお、WordPress のコアは MySQL・MariaDB を対象にしており、PostgreSQL は公式にはサポートされていません。この記事は、WordPress と並べて動かす別のアプリケーションなどで PostgreSQL を使う場面を想定しています。トリガーやファンクションの作成はテーブルの動きを変える操作なので、本番に入れる前にステージング環境で試し、バックアップを取ってから適用してください。

ファンクションの基本

PostgreSQL のファンクション（関数）は CREATE FUNCTION で作ります。簡単な計算なら SQL だけで書けます。

CREATE OR REPLACE FUNCTION tax_included(price numeric, rate numeric DEFAULT 0.10)
RETURNS numeric
LANGUAGE sql
IMMUTABLE
AS $$
  SELECT round(price * (1 + rate));
$$;

SELECT tax_included(1200);  -- 1320

$$ ... $$ は本文を囲む記号で、中に単一引用符があってもエスケープせずに書けます。IMMUTABLE は「同じ引数なら常に同じ結果を返す」という宣言で、ほかに STABLE（1つのクエリの中では結果が変わらない）、VOLATILE（毎回変わりうる。既定値）があります。宣言を実際の動きより強くすると、プランナーが結果を使い回して誤った値を返すことがあるため、迷ったら既定の VOLATILE のままにします。

条件分岐やエラーを使う処理は、PL/pgSQL で書きます。

CREATE OR REPLACE FUNCTION assert_positive(val numeric)
RETURNS void
LANGUAGE plpgsql
AS $$
BEGIN
  IF val IS NULL OR val <= 0 THEN
    RAISE EXCEPTION '値は正の数である必要があります: %', val;
  END IF;
END;
$$;

PostgreSQL 11 以降には CREATE PROCEDURE もあり、CALL で呼び出します。プロシージャは処理の途中で COMMIT できるため、大量のデータを区切りながら処理するバッチに向いています。トリガーから呼ぶのはファンクションです。

トリガーの仕組み

トリガーのファンクションは、引数を取らず RETURNS trigger と宣言します。中では次の特別な変数が使えます。

NEW：INSERT・UPDATE で、これから書き込まれる行
OLD：UPDATE・DELETE で、変更前の行
TG_OP：発生した操作（'INSERT'、'UPDATE'、'DELETE'、'TRUNCATE'）
TG_TABLE_NAME、TG_TABLE_SCHEMA：トリガーが付いたテーブル名とスキーマ名

どのタイミングで動かすかは CREATE TRIGGER で決めます。

BEFORE：書き込みの前に動く。NEW を書き換えて、保存される値を変えられる。NULL を返すと、その行の操作を取りやめる
AFTER：書き込みの後に動く。戻り値は無視される。別のテーブルへの記録など、確定した変更に反応する処理に使う
FOR EACH ROW / FOR EACH STATEMENT：行ごとに動くか、SQL 文1回につき1回動くか

例1：更新日時を自動で入れる

最もよく使うのが、行を更新したときに updated_at を現在時刻にするトリガーです。アプリケーション側で入れ忘れても、データベースが値を保証します。

CREATE TABLE posts (
  id         bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  title      text NOT NULL,
  body       text NOT NULL DEFAULT '',
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now()
);

CREATE OR REPLACE FUNCTION set_updated_at()
RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
  NEW.updated_at := now();
  RETURN NEW;
END;
$$;

CREATE TRIGGER trg_posts_updated_at
  BEFORE UPDATE ON posts
  FOR EACH ROW
  WHEN (OLD.* IS DISTINCT FROM NEW.*)
  EXECUTE FUNCTION set_updated_at();

ポイントは3つあります。BEFORE トリガーでは RETURN NEW; を忘れないこと（NULL を返すと更新そのものが行われません）。WHEN 句で、値が実際に変わったときだけ動かしていること。そして、このファンクションは特定のテーブルに依存しないため、updated_at 列を持つ別のテーブルにもそのまま使い回せることです。

EXECUTE FUNCTION は PostgreSQL 11 以降の書き方で、それより前は EXECUTE PROCEDURE と書きます。now() はトランザクションの開始時刻を返すため、1つのトランザクションで更新した行は同じ時刻になります。

例2：変更履歴を監査ログに残す

誰がいつ何を変えたかを残すには、AFTER トリガーで履歴テーブルに書き込みます。変更前後の行を jsonb で保存すると、テーブルの列が増えても履歴テーブルを変えずに済みます。

CREATE TABLE audit_log (
  id         bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  table_name text        NOT NULL,
  operation  text        NOT NULL,
  old_row    jsonb,
  new_row    jsonb,
  changed_by text        NOT NULL DEFAULT current_user,
  changed_at timestamptz NOT NULL DEFAULT now()
);

CREATE OR REPLACE FUNCTION write_audit_log()
RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
  INSERT INTO audit_log (table_name, operation, old_row, new_row)
  VALUES (
    TG_TABLE_NAME,
    TG_OP,
    CASE WHEN TG_OP IN ('UPDATE', 'DELETE') THEN to_jsonb(OLD) END,
    CASE WHEN TG_OP IN ('INSERT', 'UPDATE') THEN to_jsonb(NEW) END
  );
  RETURN NULL;  -- AFTER トリガーの戻り値は使われない
END;
$$;

CREATE TRIGGER trg_posts_audit
  AFTER INSERT OR UPDATE OR DELETE ON posts
  FOR EACH ROW
  EXECUTE FUNCTION write_audit_log();

changed_by に入るのはデータベースに接続しているロール名です。アプリケーションが1つのロールで接続している場合は、実際の利用者まではわかりません。利用者を記録したいなら、アプリケーションからトランザクションごとに SET LOCAL で設定値を渡し、トリガー内で current_setting('app.user_id', true) のように読み取る方法があります。

監査ログには個人情報やパスワードのハッシュなど、残すべきでない列が含まれることがあります。to_jsonb(NEW) - 'password_hash' のように除外する列を決め、ログの保存期間と閲覧できる人も決めておいてください。

SECURITY DEFINER を使うときの注意

監査ログのテーブルに、一般のロールが直接書き込めないようにしたい場合は、ファンクションに SECURITY DEFINER を付けて、作成者の権限で実行させる方法があります。このとき、search_path を固定しないと、悪意のあるロールが同名の関数やテーブルを別のスキーマに作って処理を差し替える危険があります。

ALTER FUNCTION write_audit_log()
  SECURITY DEFINER
  SET search_path = public, pg_temp;

search_path には信頼できるスキーマだけを並べ、pg_temp を最後に置くのが公式ドキュメントの推奨する形です。PostgreSQL 14 以前は、既定で誰でも public スキーマにオブジェクトを作れるため、その場合は書き込みを制限した専用のスキーマを使うほうが安全です。あわせて、ファンクションの実行権限（EXECUTE）を必要なロールに絞ります。

もう1つの落とし穴は、SECURITY DEFINER のファンクションの中では current_user がファンクションの所有者になることです。このままでは changed_by に所有者の名前が記録されてしまうため、接続したロールを残したい場合は、列の既定値に頼らず、ファンクションの INSERT で session_user を明示して入れてください。

動作確認とトラブルの調べ方

トリガーを作ったら、トランザクションの中で試し、結果を確認してから取り消すと安全です。

BEGIN;
UPDATE posts SET title = '確認用' WHERE id = 1;
SELECT updated_at FROM posts WHERE id = 1;
SELECT operation, new_row ->> 'title' FROM audit_log ORDER BY id DESC LIMIT 1;
ROLLBACK;

テーブルに付いているトリガーは、psql の \d posts か、次のクエリで確認できます。

SELECT tgname, tgenabled
FROM pg_trigger
WHERE tgrelid = 'posts'::regclass AND NOT tgisinternal;

うまく動かないときに多い原因は、次のとおりです。

値が変わらない：BEFORE ではなく AFTER トリガーで NEW を書き換えている。AFTER では保存後なので反映されない
更新が消える：BEFORE トリガーで RETURN NEW; が抜けている、または条件によって NULL を返している
無限に再帰する、スタックの深さのエラーが出る：トリガーの中で同じテーブルを UPDATE している。BEFORE トリガーで NEW を書き換える形に直すか、pg_trigger_depth() で深さを見て抜ける
大量更新が遅い：行ごとのトリガーが行数分動いている。一括処理では FOR EACH STATEMENT と遷移テーブル（REFERENCING NEW TABLE AS ...、PostgreSQL 10 以降）でまとめて処理できないか検討する

デバッグ中は RAISE NOTICE '値: %', NEW.title; を入れると、psql に値を表示できます。確認が済んだら削除してください。同じテーブル・同じタイミングに複数のトリガーがある場合は、名前のアルファベット順に実行されます。順番が意味を持つときは、名前の付け方で制御します。

トリガーの使いすぎにも注意が要ります。処理がアプリケーションのコードから見えなくなるため、チームで「どのテーブルにどのトリガーがあるか」を共有し、マイグレーションのファイルで管理しておくと、後から調べる人が迷いません。トリガーの細かな仕様はバージョンごとに追加されています（2026年10月現在）。たとえば CREATE OR REPLACE TRIGGER は PostgreSQL 14 以降で使えるため、使うバージョンのドキュメントも確認してください。

まとめ

PostgreSQL のトリガーは、RETURNS trigger のファンクションと CREATE TRIGGER の2つで設定する
値を書き換えるなら BEFORE（RETURN NEW を忘れない）、確定した変更に反応するなら AFTER
更新日時のトリガーは WHEN (OLD.* IS DISTINCT FROM NEW.*) で無駄な動作を減らせる
監査ログは jsonb で前後の行を残し、残すべきでない列は除外する
SECURITY DEFINER を使うなら search_path を固定し、実行権限を絞る
試すときは BEGIN と ROLLBACK で囲み、本番の前にステージングで確認する