PostgreSQL | SQLite＋MySQL経験者向け、30日で習得するPostgreSQL：プロレベル運用 － Day25 トリガー

Day25 後半のゴール

「“何でもトリガー”を卒業して、“設計された自動処理”にする」

前半で、トリガーの基本構造と監査ログの例は掴めました。
後半では、「どこまでトリガーに任せるか」「どんな書き方をすると危ないか」「パフォーマンスとセキュリティをどう意識するか」を、もう少し実務寄りに整理していきます。

BEFOREトリガーで“値を自動補完する”

「アプリに任せると漏れがちな処理をDB側に寄せる」

よくあるパターンが、「created_at / updated_at を毎回ちゃんと入れたい」という要件です。
アプリ側で毎回セットしてもいいのですが、書き忘れ・バグ・複数アプリからのアクセスなどを考えると、DB側で自動化したくなります。

ここで使いやすいのが BEFORE トリガーです。
INSERT や UPDATE が実際にテーブルに反映される“前”に、NEW の値を書き換えられます。

例えば、posts テーブルに created_at / updated_at を自動で入れるトリガーを考えます。

CREATE TABLE posts (
  id          bigserial PRIMARY KEY,
  title       text NOT NULL,
  body        text NOT NULL,
  created_at  timestamptz NOT NULL,
  updated_at  timestamptz NOT NULL
);

CREATE TABLE posts (
  id          bigserial PRIMARY KEY,
  title       text NOT NULL,
  body        text NOT NULL,
  created_at  timestamptz NOT NULL,
  updated_at  timestamptz NOT NULL
);

このままだと、INSERT 時に created_at / updated_at を必ず指定しないとエラーになります。
ここに BEFORE トリガーを仕込んで、「足りないときは自動で埋める」ようにします。

自動補完トリガー関数の例

CREATE OR REPLACE FUNCTION trg_posts_set_timestamps()
RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
  IF TG_OP = 'INSERT' THEN
    IF NEW.created_at IS NULL THEN
      NEW.created_at := now();
    END IF;
    IF NEW.updated_at IS NULL THEN
      NEW.updated_at := now();
    END IF;
  ELSIF TG_OP = 'UPDATE' THEN
    NEW.updated_at := now();
  END IF;

  RETURN NEW;
END;
$$;

CREATE OR REPLACE FUNCTION trg_posts_set_timestamps()
RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
  IF TG_OP = 'INSERT' THEN
    IF NEW.created_at IS NULL THEN
      NEW.created_at := now();
    END IF;
    IF NEW.updated_at IS NULL THEN
      NEW.updated_at := now();
    END IF;
  ELSIF TG_OP = 'UPDATE' THEN
    NEW.updated_at := now();
  END IF;

  RETURN NEW;
END;
$$;

ここで新しく出てきたポイントがいくつかあります。

TG_OP は、「このトリガーがどの操作で呼ばれたか」を表す特別な変数です。
INSERT なら ‘INSERT’、UPDATE なら ‘UPDATE’ が入っています。

NEW は、「これからテーブルに書き込まれる行」です。
BEFORE トリガーでは、この NEW を書き換えることで、実際に保存される値を変えられます。

INSERT のときは、created_at / updated_at が NULL なら now() を入れる。
UPDATE のときは、updated_at を now() に上書きする。

最後に RETURN NEW; で、「最終的に保存したい行」を返しています。

トリガーの紐づけ

CREATE TRIGGER posts_set_timestamps
BEFORE INSERT OR UPDATE ON posts
FOR EACH ROW
EXECUTE FUNCTION trg_posts_set_timestamps();

CREATE TRIGGER posts_set_timestamps
BEFORE INSERT OR UPDATE ON posts
FOR EACH ROW
EXECUTE FUNCTION trg_posts_set_timestamps();

これで、アプリ側は created_at / updated_at を意識しなくても、
INSERT / UPDATE のたびに自動で適切な値が入るようになります。

ここでの重要ポイントは、「“絶対に全レコードで守りたいルール”は、トリガーでDB側に寄せると漏れにくい」ということです。

集計テーブルをトリガーで保つかどうか

「“便利だけど重くなりがち”な代表例」

次に、少し難易度が上がるパターンとして「集計テーブルの自動更新」を考えます。

例えば、orders テーブルと、ユーザーごとの合計金額を持つ user_stats テーブルがあるとします。

CREATE TABLE orders (
  id        bigserial PRIMARY KEY,
  user_id   bigint NOT NULL,
  amount    integer NOT NULL
);

CREATE TABLE user_stats (
  user_id        bigint PRIMARY KEY,
  total_amount   integer NOT NULL DEFAULT 0
);

CREATE TABLE orders (
  id        bigserial PRIMARY KEY,
  user_id   bigint NOT NULL,
  amount    integer NOT NULL
);

CREATE TABLE user_stats (
  user_id        bigint PRIMARY KEY,
  total_amount   integer NOT NULL DEFAULT 0
);

ここで、「注文が入るたびに user_stats.total_amount を増やしたい」と考えると、
トリガーでこう書きたくなります。

集計更新トリガーのイメージ

CREATE OR REPLACE FUNCTION trg_orders_update_user_stats()
RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
  IF TG_OP = 'INSERT' THEN
    INSERT INTO user_stats (user_id, total_amount)
    VALUES (NEW.user_id, NEW.amount)
    ON CONFLICT (user_id)
    DO UPDATE SET total_amount = user_stats.total_amount + EXCLUDED.total_amount;
  ELSIF TG_OP = 'DELETE' THEN
    UPDATE user_stats
       SET total_amount = total_amount - OLD.amount
     WHERE user_id = OLD.user_id;
  END IF;

  RETURN NEW;
END;
$$;

CREATE OR REPLACE FUNCTION trg_orders_update_user_stats()
RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
  IF TG_OP = 'INSERT' THEN
    INSERT INTO user_stats (user_id, total_amount)
    VALUES (NEW.user_id, NEW.amount)
    ON CONFLICT (user_id)
    DO UPDATE SET total_amount = user_stats.total_amount + EXCLUDED.total_amount;
  ELSIF TG_OP = 'DELETE' THEN
    UPDATE user_stats
       SET total_amount = total_amount - OLD.amount
     WHERE user_id = OLD.user_id;
  END IF;

  RETURN NEW;
END;
$$;

そして、

CREATE TRIGGER orders_update_user_stats
AFTER INSERT OR DELETE ON orders
FOR EACH ROW
EXECUTE FUNCTION trg_orders_update_user_stats();

CREATE TRIGGER orders_update_user_stats
AFTER INSERT OR DELETE ON orders
FOR EACH ROW
EXECUTE FUNCTION trg_orders_update_user_stats();

とすれば、「注文が増えたり消えたりするたびに、集計テーブルが自動で更新される」状態になります。

これは一見とても便利ですが、ここで考えるべきことが増えます。

大量の INSERT / DELETE が発生すると、そのたびに user_stats への更新が走る。
トランザクションの中で大量の行を処理すると、トリガーもその回数だけ呼ばれる。
ロックの競合やパフォーマンス劣化の原因になりやすい。

つまり、「リアルタイムに集計を保ちたい」という要求と、「負荷・ロックを抑えたい」という要求のバランスを取る必要が出てきます。

実務では、「トリガーでリアルタイム更新」ではなく、「バッチで定期集計」「マテリアライズドビュー＋リフレッシュ」など、別の手段を選ぶことも多いです。

ここでの学びは、「トリガーは“できる”けど、“やるべきかどうか”は別問題」ということです。

トリガーがパフォーマンスに与える影響

「1回のINSERTが、実は何回分の仕事をしているか」

トリガーは、アプリから見ると「ただの INSERT / UPDATE / DELETE」に見えます。
でも、DBの中では「そのたびに追加の処理」が走っています。

例えば、1回の UPDATE で10行更新し、
そのテーブルに AFTER UPDATE FOR EACH ROW のトリガーが1つ付いているとします。

このとき、

UPDATE 自体の処理
＋ 10行分のトリガー関数呼び出し
＋ トリガー関数の中のSQL（INSERT / UPDATE など）

が全部1トランザクションの中で動きます。

トリガー関数の中で重い処理（複雑なSELECT、別テーブルへの大量INSERTなど）をしていると、
「アプリから見た1回のUPDATE」が、想像以上に重くなります。

だから、プロレベル運用では、次のような方針をよく取ります。

トリガーの中身はできるだけ軽く・短く保つ。
重い処理は、キューに積んで別プロセス（ジョブキュー、バッチ）に任せる。
トリガーの有無・内容を、性能調査や障害調査のときに必ず確認する。

「トリガーは“隠れたコスト”になりやすい」という感覚を持っておくと、設計の判断が変わってきます。

セキュリティとトリガー

「“勝手に動くコード”だからこそ、誰が書けるか・何をするかが重要」

トリガーは、「アプリが意識していなくても勝手に動くコード」です。
だからこそ、セキュリティの観点では次のような点が重要になります。

まず、「誰がトリガー関数を作れるか・変更できるか」を絞ること。
トリガー関数は PL/pgSQL で自由にSQLを書けるので、
悪意のあるコードを書けば、データの改ざん・削除・漏えいの入り口になりえます。

次に、「トリガーの中で何をしているか」をレビューすること。
外部テーブルへのINSERT、特定ユーザーだけの特別扱い、ログの改ざんなど、
“見えにくい場所での不正”が起きやすいポイントでもあります。

さらに、「監査ログをトリガーで書く場合、そのトリガー自体が改ざんされていないか」を監視することも大事です。
監査のための仕組みが、逆に攻撃者にとっての“消したい対象”になるからです。

実務では、

トリガー定義・トリガー関数のコードをリポジトリで管理する。
本番環境でのトリガー追加・変更は、レビューと承認フローを通す。
定期的に「どのテーブルにどんなトリガーが付いているか」を棚卸しする。

といった運用を組み合わせて、「勝手に動くコード」をコントロールします。

「トリガーでやる／やらない」を決めるチェックポイント

「自動化の気持ちよさと、見通しの良さのバランス」

最後に、「この処理、トリガーでやるべきか？」と迷ったときに、自分に投げてほしい問いをまとめます。

その処理は、「テーブル操作と絶対にセットで行われるべきもの」か
例：監査ログ、タイムスタンプ自動補完、整合性維持のための最小限の更新など。

その処理は、「アプリ側で書き忘れると致命傷になる」か
例：セキュリティ・監査・法令順守に関わる記録など。

その処理は、「トリガーの中でやっても軽く済む」か
重い集計・外部API呼び出し・大量の書き込みなどは、本当にトリガーでやるべきかを疑う。

その処理は、「将来の自分や他人が“ここで動いている”と気づける」か
ドキュメント・命名・コメントで、トリガーの存在を見える化できているか。

このあたりを自分なりに言語化できるようになると、
「なんとなく便利そうだからトリガー」から、「設計された自動処理」として使えるようになります。

Day25 後半のまとめ

トリガーは BEFORE トリガーで NEW を書き換えることで created_at / updated_at の自動補完のような「全レコードで必ず守りたいルール」をDB側に埋め込める一方、集計テーブルのリアルタイム更新のような重い処理を入れ始めると、1回のINSERT/UPDATEが裏で大量の仕事を抱え込み、ロックやパフォーマンス問題の原因になりやすい“隠れコスト”にもなる。
さらに、トリガーは「勝手に動くコード」なので、誰が定義・変更できるか、何をしているかをレビュー・監査しないとセキュリティホールにもなりうるため、「テーブル操作と絶対にセットであるべき軽い処理（監査・タイムスタンプ・最小限の整合性維持）だけをトリガーに任せ、重い処理やビジネスロジックはアプリや別の仕組みに逃がす」という線引きを自分の中に持つことが、Day25 後半の着地点になります。