51 触发器

使用行级和语句级触发器维护时间戳并审计变更。

触发器是一种函数，当表上发生特定事件时，PostgreSQL 会自动调用它——事件可以是 INSERT、UPDATE、DELETE 或 TRUNCATE；触发时机可以是行变更之前或之后（BEFORE 或 AFTER），作用范围可以是每一行，也可以是整条语句。触发器是强制执行类型系统无法表达的不变式、审计变更、出于性能考虑进行反规范化，以及保持 updated_at 时间戳最新的标准工具。

本章要点

BEFORE 与 AFTER 的区别
行级（FOR EACH ROW）与语句级（FOR EACH STATEMENT）
NEW 和 OLD 行变量
典型示例：自动更新 updated_at
向另一个表写入审计日志的触发器

示例

示例 51.1 触发器

-- Setup
DROP TABLE IF EXISTS articles_example;
DROP TABLE IF EXISTS articles_audit;
CREATE TABLE articles_example (
  id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  title       text NOT NULL,
  body        text NOT NULL,
  created_at  timestamptz NOT NULL DEFAULT current_timestamp,
  updated_at  timestamptz NOT NULL DEFAULT current_timestamp
);

CREATE TABLE articles_audit (
  id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  article_id  bigint NOT NULL,
  action      text   NOT NULL,
  by_user     text   NOT NULL DEFAULT current_user,
  at          timestamptz NOT NULL DEFAULT current_timestamp,
  old_row     jsonb,
  new_row     jsonb
);

-- Trigger function: bump updated_at before every UPDATE
CREATE OR REPLACE FUNCTION set_updated_at()
RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
  NEW.updated_at := current_timestamp;
  RETURN NEW;
END;
$$;

CREATE TRIGGER articles_updated_at
BEFORE UPDATE ON articles_example
FOR EACH ROW
EXECUTE FUNCTION set_updated_at();

-- Trigger function: write to the audit log after every change
CREATE OR REPLACE FUNCTION audit_article_change()
RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
  INSERT INTO articles_audit (article_id, action, old_row, new_row)
  VALUES (
    COALESCE(NEW.id, OLD.id),
    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 trigger return value is ignored
END;
$$;

CREATE TRIGGER articles_audit
AFTER INSERT OR UPDATE OR DELETE ON articles_example
FOR EACH ROW
EXECUTE FUNCTION audit_article_change();

-- Exercise the triggers
INSERT INTO articles_example (title, body) VALUES ('Hello', 'world');
UPDATE articles_example SET body = 'edited world' WHERE title = 'Hello';
DELETE FROM articles_example;

SELECT * FROM articles_audit ORDER BY id;

-- Cleanup
DROP TRIGGER IF EXISTS articles_updated_at ON articles_example;
DROP TRIGGER IF EXISTS articles_audit ON articles_example;
DROP FUNCTION IF EXISTS set_updated_at();
DROP FUNCTION IF EXISTS audit_article_change();
DROP TABLE IF EXISTS articles_example;
DROP TABLE IF EXISTS articles_audit;

触发器由两部分组成：一个触发器函数（返回 trigger），以及一个将该函数绑定到表和事件的触发器。在函数内部：

NEW 是新行（适用于 INSERT 和 UPDATE）。
OLD 是旧行（适用于 UPDATE 和 DELETE）。
TG_OP 表示操作（'INSERT'、'UPDATE'、'DELETE'、'TRUNCATE'）。
还可以使用 TG_TABLE_NAME 等变量来编写可复用的触发器。

对于 BEFORE ROW 的 INSERT/UPDATE 触发器，应返回 NEW（它可能已被修改）；对于 DELETE，返回 OLD。返回 NULL 会跳过该行的操作。对于 AFTER ROW 触发器，返回值会被忽略——因为操作已经发生。语句级触发器（FOR EACH STATEMENT）每条语句只触发一次，与行数无关，也没有 NEW/OLD。

自动更新 updated_at 的触发器是世界上最常见的触发器。定义一个触发器函数，把它绑定到每个包含 updated_at 列的表上。

运行示例

运行方式：

$ psql -f source/triggers.sql postgres
DROP TABLE
DROP TABLE
CREATE TABLE
CREATE TABLE
CREATE FUNCTION
CREATE TRIGGER
CREATE FUNCTION
CREATE TRIGGER
INSERT 0 1
UPDATE 1
DELETE 1
 id | article_id | action  | by_user  |              at               | old_row | new_row
----+------------+---------+----------+-------------------------------+---------+---------
  1 |          1 | INSERT  | your_user | 2026-xx-xx ...                |         | {"id":1,...}
  2 |          1 | UPDATE  | your_user | 2026-xx-xx ...                | {...}   | {...}
  3 |          1 | DELETE  | your_user | 2026-xx-xx ...                | {...}   |
(3 rows)
 ...

常见陷阱

返回 NULL 的 BEFORE 触发器会悄无声息地吞掉该行——INSERT/UPDATE 会在不报错的情况下被跳过。除非你确实要跳过该行，否则对 INSERT/UPDATE 返回 NEW，对 DELETE 返回 OLD。
触发器与触发它的语句在同一事务中运行。触发器中的耗时逻辑会拖慢对该表的每次修改。
触发器在 COPY ... FROM 时默认不会触发？其实会触发——但 TRUNCATE 不会触发行级触发器（只触发语句级触发器）。请据此安排。
把业务逻辑放进触发器后，应用代码中就很难发现这些逻辑。许多团队会把触发器限制在基础设施关注点（updated_at、审计）上，并把业务逻辑留在应用程序中。

提示

在触发器内使用 pg_trigger_depth() 检测递归。如果触发器更新了触发它的表，就可能陷入循环——可用 IF pg_trigger_depth() > 1 THEN RETURN NEW; END IF; 防止这种情况。

动手练习

脚本最后会删除演示表和触发器函数。继续之前，先根据示例重新创建时间戳触发器函数。给 fruits 添加 updated_at 列，并绑定上面的触发器。更新一行，确认 updated_at 发生变化。然后添加一个 BEFORE INSERT 触发器，让它自动将 name 转为大写，观察它的效果。

51 触发器

本章要点

示例

运行示例

常见陷阱

下载