PHP小码农

Appearance

触发器函数编写 ​

触发器函数（trigger function）是「触发器脑袋里的那段代码」——它用 CREATE FUNCTION 创建、声明为零参数、返回 trigger 类型，由 CREATE TRIGGER 绑定到表上。它与普通函数有四处关键差别：调用方式（只能被触发器管理器回调）、参数传递（走 TG_ARGV 而非形参）、自动可用的 TG_ 系列变量、返回值语义随时机与层级变化。本节把这些差别讲透，并给出可复用的编写模板。

前置知识

阅读本节前，建议先了解：过渡表（REFERENCING）、行级触发器（FOR EACH ROW）

基础概念 ​

触发器函数 vs 普通函数 ​

对比项普通函数触发器函数
声明方式CREATE FUNCTION f(a int) RETURNS intCREATE FUNCTION f() RETURNS trigger
参数有若干形参必须零参数，参数走 TG_ARGV
调用者你自己（SELECT、CALL、表达式中）只能由触发器管理器在事件发生时回调
能否被 SELECT 调用可以不可以（0A000 报错）
自动变量无NEW / OLD / TG_* 共 12 个
返回值含义返回给调用方语义随时机与层级变化（见下表）
典型波动性视情况默认 VOLATILE，不应改成 STABLE / IMMUTABLE
text
CREATE TRIGGER（绑定）              CREATE FUNCTION ... RETURNS trigger（逻辑）
├─ 选表 / 视图                      ├─ 零参数
├─ 选 BEFORE / AFTER / INSTEAD OF   ├─ 读 NEW / OLD / TG_*
├─ 选 FOR EACH ROW / STATEMENT      ├─ 可读写表、可抛错
├─ 选事件 INSERT / UPDATE / DELETE   └─ 返回 NULL 或一行
├─ 可传参 f('a', 'b')  → TG_ARGV
└─ 可加 WHEN 条件

这种「绑定」与「逻辑」的分离，让同一个函数能被多张表、多个事件复用——只要函数内部用 TG_TABLE_NAME、TG_OP 做分支。

必须零参数、必须返回 trigger ​

text
-- 正确
CREATE FUNCTION f() RETURNS trigger AS $$ ... $$ LANGUAGE plpgsql;

-- 错误！有参数
CREATE FUNCTION f(a int) RETURNS trigger AS $$ ... $$ LANGUAGE plpgsql;

-- 错误！返回类型不是 trigger
CREATE FUNCTION f() RETURNS void AS $$ ... $$ LANGUAGE plpgsql;

官方原文强调：

Note that the function must be declared with no arguments even if it expects to receive some arguments specified in CREATE TRIGGER — such arguments are passed via TG_ARGV.

也就是说，CREATE TRIGGER ... EXECUTE FUNCTION f('x') 里的 'x' 不会成为函数形参，而是被放进 TG_ARGV[0]。函数签名始终是空的括号。

返回类型必须精确匹配

把返回值写成 SETOF trigger、trigger 的数组、或是别的类型，CREATE TRIGGER 会拒绝执行。数据变更触发器用 trigger，事件触发器用 event_trigger，两者不可混用。

返回值语义总表 ​

这是本站读者最容易踩坑的地方。官方对返回值的定义必须逐时机、逐层级对照：

触发器类型返回 NULL返回非 NULL
行级 BEFORE跳过该行（后续同行触发器不再执行，该行的 INSERT/UPDATE/DELETE 不发生）操作以该行值继续；返回不同于原 NEW 的行会改写将要写入的行
行级 BEFORE（DELETE）跳过该行删除返回值无直接影响，但必须非 NULL 才允许继续；惯用返回 OLD
行级 AFTER返回值总被忽略返回值总被忽略
语句级 BEFORE返回值总被忽略返回值总被忽略
语句级 AFTER返回值总被忽略返回值总被忽略
行级 INSTEAD OF「我没有修改任何底层数据」→ 该行不计入影响行数，后续触发器不执行「操作已完成」→ 影响行数 +1，并决定 RETURNING 输出

官方原文（逐句对应上表）：

A trigger function must return either NULL or a record/row value having exactly the structure of the table the trigger was fired for.

Row-level triggers fired BEFORE can return null to signal the trigger manager to skip the rest of the operation for this row... Returning a row value different from the original value of NEW alters the row that will be inserted or updated.

In the case of a before-trigger on DELETE, the returned value has no direct effect, but it has to be nonnull to allow the trigger action to proceed.

INSTEAD OF triggers... can return null to signal that they did not perform any updates...

The return value of a row-level trigger fired AFTER or a statement-level trigger fired BEFORE or AFTER is always ignored; it might as well be null.

三条最易错的结论

  1. AFTER 行级触发器的返回值完全无效——返回 NEW 不会改变任何东西，只是给人错觉。
  2. BEFORE 行级忘记 RETURN 时函数返回 NULL，等价于静默跳过所有行（UPDATE 0 而应用毫无察觉）。
  3. 所有触发器都能通过 RAISE EXCEPTION 中止整个操作，哪怕它的返回值被忽略。

返回值的标准模板 ​

sql
-- 行级通用模板：DELETE 返回 OLD，其余返回 NEW
IF TG_OP = 'DELETE' THEN
  RETURN OLD;
END IF;
RETURN NEW;
sql
-- 语句级 / 行级 AFTER 模板：返回 NULL 即可（反正被忽略）
RETURN NULL;

TG_ 系列变量（复用视角） ​

变量类型用途举例
NEWrecord改写将写入的行（仅行级 BEFORE 有效）
OLDrecord对比旧值、计算差异（始终只读）
TG_NAMEname日志里记录「哪个触发器」
TG_WHENtextBEFORE / AFTER / INSTEAD OF
TG_LEVELtextROW / STATEMENT
TG_OPtext统一分支的核心：INSERT / UPDATE / DELETE / TRUNCATE
TG_RELIDoid需要 pg_class OID 时使用
TG_TABLE_NAMEname一个函数服务多表时区分表
TG_TABLE_SCHEMAname多 schema 部署时区分
TG_NARGSinteger校验参数个数
TG_ARGVtext[]读取 CREATE TRIGGER 传入的配置

TG_RELNAME 已弃用，新代码统一用 TG_TABLE_NAME。TG_ARGV 下标从 0 开始，越界（小于 0 或不小于 TG_NARGS）返回 NULL。

语法与用法 ​

完整语法原型 ​

text
CREATE [ OR REPLACE ] FUNCTION name ()          -- 参数列表必须为空
    RETURNS trigger                             -- 数据变更触发器
    { LANGUAGE lang_name
    | TRANSFORM { FOR TYPE type_name } [, ... ]
    | { IMMUTABLE | STABLE | VOLATILE }         -- 触发器函数应保持 VOLATILE
    | [ NOT ] LEAKPROOF
    | { CALLED ON NULL INPUT | RETURNS NULL ON NULL INPUT | STRICT }
    | { [ EXTERNAL ] SECURITY INVOKER | [ EXTERNAL ] SECURITY DEFINER }
    | PARALLEL { UNSAFE | RESTRICTED | SAFE }
    | COST execution_cost
    | SET configuration_parameter { TO value | = value | FROM CURRENT }
    | AS 'definition'
    } ...

-- 事件触发器用另一套返回类型：
CREATE FUNCTION name () RETURNS event_trigger ...

关键点：

  • 函数名后的括号必须为空，不能有参数，也不能写 OUT 参数。
  • RETURNS trigger 是数据变更触发器的唯一合法返回类型。
  • IMMUTABLE / STABLE 与触发器场景冲突（见后文「波动性」）。
  • SECURITY DEFINER 会改变触发器运行时使用的权限身份。

最小可运行示例 ​

sql
CREATE TABLE emp (
  empname   text,
  salary    integer,
  last_date timestamp,
  last_user text
);

CREATE OR REPLACE FUNCTION emp_stamp() RETURNS trigger
LANGUAGE plpgsql AS $$
BEGIN
  -- 校验
  IF NEW.empname IS NULL THEN
    RAISE EXCEPTION 'empname cannot be null';
  END IF;
  IF NEW.salary IS NULL THEN
    RAISE EXCEPTION '% cannot have null salary', NEW.empname;
  END IF;
  IF NEW.salary < 0 THEN
    RAISE EXCEPTION '% cannot have a negative salary', NEW.empname;
  END IF;

  -- 戳记
  NEW.last_date := current_timestamp;
  NEW.last_user := current_user;
  RETURN NEW;
END;
$$;

CREATE TRIGGER emp_stamp
  BEFORE INSERT OR UPDATE ON emp
  FOR EACH ROW EXECUTE FUNCTION emp_stamp();
sql
INSERT INTO emp (empname, salary) VALUES ('alice', 40000);
SELECT empname, salary, last_user FROM emp;
text
 empname | salary | last_user
---------+--------+-----------
 alice   |  40000 | ch
(1 row)

绑定触发器 ​

text
CREATE [ OR REPLACE ] [ CONSTRAINT ] TRIGGER name
    { BEFORE | AFTER | INSTEAD OF } { event [ OR ... ] }
    ON table_name
    [ REFERENCING { { OLD | NEW } TABLE [ AS ] transition_relation_name } [ ... ] ]
    [ FOR [ EACH ] { ROW | STATEMENT } ]
    [ WHEN ( condition ) ]
    EXECUTE { FUNCTION | PROCEDURE } function_name ( arguments )

FUNCTION 与 PROCEDURE 关键字在 EXECUTE 后等价，但被调用的对象必须是函数（不是过程）。

详细说明 ​

零参数约束与 TG_ARGV 读取 ​

参数以字符串常量传入，函数里按下标读取：

sql
CREATE OR REPLACE FUNCTION log_change() RETURNS trigger
LANGUAGE plpgsql AS $$
BEGIN
  RAISE NOTICE '参数个数 = %', TG_NARGS;
  RAISE NOTICE '第 1 个参数 = %', TG_ARGV[0];
  RAISE NOTICE '第 2 个参数 = %', TG_ARGV[1];
  RETURN NULL;
END;
$$;
sql
CREATE TRIGGER trg_log
  AFTER INSERT ON some_table
  FOR EACH ROW EXECUTE FUNCTION log_change('audit', 'products');

INSERT INTO some_table DEFAULT VALUES;
text
NOTICE:  参数个数 = 2
NOTICE:  第 1 个参数 = audit
NOTICE:  第 2 个参数 = products
NOTICE:  参数个数 = 2

防御式写法：先校验个数，再看内容，避免下标越界带来的隐性 NULL。

sql
CREATE OR REPLACE FUNCTION stamp_cols() RETURNS trigger
LANGUAGE plpgsql AS $$
BEGIN
  IF TG_NARGS <> 1 THEN
    RAISE EXCEPTION '函数 % 需要 1 个参数: <列名>', TG_NAME;
  END IF;

  IF TG_ARGV[0] = 'updated_at' THEN
    NEW.updated_at := now();
  ELSIF TG_ARGV[0] = 'created_at' THEN
    NEW.created_at := now();
  ELSIF TG_ARGV[0] = 'updated_by' THEN
    NEW.updated_by := current_user;
  ELSE
    RAISE EXCEPTION '不支持的列名参数: %', TG_ARGV[0];
  END IF;

  RETURN NEW;
END;
$$;

不要用位置参数写「万能列名」

只有当列名来自可信配置时才考虑把 TG_ARGV 当列名用。若列名可能来自用户输入，必须先经过白名单校验，或改用显式分支。动态拼接列名必须用 format(... %I)：

sql
-- 危险：直接拼接
EXECUTE 'SELECT ($1).' || TG_ARGV[0] INTO v USING NEW;

-- 正确：只允许白名单内的标识符
IF TG_ARGV[0] NOT IN ('updated_at', 'created_at') THEN
  RAISE EXCEPTION '非法列名: %', TG_ARGV[0];
END IF;
EXECUTE format('SELECT ($1).%I', TG_ARGV[0]) INTO v USING NEW;

返回值语义逐时机/层级对照 ​

用一个函数把每种情形的返回值都演示出来：

sql
CREATE OR REPLACE FUNCTION demo_returns() RETURNS trigger
LANGUAGE plpgsql AS $$
BEGIN
  IF TG_LEVEL = 'STATEMENT' THEN
    -- 语句级：返回什么都被忽略，惯例返回 NULL
    RETURN NULL;
  END IF;

  IF TG_WHEN = 'AFTER' THEN
    -- AFTER 行级：返回什么都被忽略，惯例返回 NULL
    RETURN NULL;
  END IF;

  -- BEFORE 行级：必须返回行
  IF TG_OP = 'DELETE' THEN
    RETURN OLD;   -- NEW 为 NULL，返回 NEW 不合理
  END IF;
  RETURN NEW;     -- 返回非 NULL 才不会被跳过
END;
$$;

三种「跳过」行为要分清：

text
BEFORE 行级返回 NULL        → 该行被跳过，计数为 0
对不需要的行在函数里 RETURN NULL → 条件式跳过
AFTER 行级返回 NULL          → 无事发生（返回 ANY 值都一样）

DELETE 触发器里 NEW 是 NULL，所以「返回 NULL 表示跳过」与「返回 NULL 表示 NEW」在语义上会撞车——官方特意提醒 NEW 在 DELETE 中为 NULL，返回它通常不合理，惯用返回 OLD。

用 TG_OP 分支的统一模板 ​

一个函数复用给 INSERT/UPDATE/DELETE 时，TG_OP 是唯一的分支依据：

sql
CREATE OR REPLACE FUNCTION audit_row() RETURNS trigger
LANGUAGE plpgsql AS $$
BEGIN
  IF TG_OP = 'DELETE' THEN
    INSERT INTO audit_log(op, changed_at, changed_by, payload)
    VALUES ('D', now(), current_user, to_jsonb(OLD));
    RETURN OLD;
  ELSIF TG_OP = 'UPDATE' THEN
    INSERT INTO audit_log(op, changed_at, changed_by, payload)
    VALUES ('U', now(), current_user, to_jsonb(NEW));
    RETURN NEW;
  ELSE
    INSERT INTO audit_log(op, changed_at, changed_by, payload)
    VALUES ('I', now(), current_user, to_jsonb(NEW));
    RETURN NEW;
  END IF;
END;
$$;

更紧凑的分支：先算「要审计的行」，再统一插入。

sql
CREATE OR REPLACE FUNCTION audit_row_v2() RETURNS trigger
LANGUAGE plpgsql AS $$
DECLARE
  v_op      char(1);
  v_payload jsonb;
BEGIN
  IF TG_OP = 'DELETE' THEN
    v_op := 'D'; v_payload := to_jsonb(OLD);
  ELSE
    v_op := CASE TG_OP WHEN 'INSERT' THEN 'I' ELSE 'U' END;
    v_payload := to_jsonb(NEW);
  END IF;

  INSERT INTO audit_log(op, changed_at, changed_by, payload)
  VALUES (v_op, now(), current_user, v_payload);

  IF TG_OP = 'DELETE' THEN
    RETURN OLD;
  END IF;
  RETURN NEW;
END;
$$;

TG_OP 也可能是 TRUNCATE，但 TRUNCATE 只能是语句级，此时应返回 NULL。

一个函数服务多表 ​

配合 TG_TABLE_NAME / TG_TABLE_SCHEMA，一个函数可绑定到多张表：

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

CREATE TRIGGER trg_touch_users
  BEFORE UPDATE ON users
  FOR EACH ROW EXECUTE FUNCTION touch_updated_at();

CREATE TRIGGER trg_touch_orders
  BEFORE UPDATE ON orders
  FOR EACH ROW EXECUTE FUNCTION touch_updated_at();

需要按表区分行为时读取表名：

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

  IF TG_TABLE_SCHEMA <> 'public' THEN
    RAISE EXCEPTION '只允许在 public schema 触发，当前为 %', TG_TABLE_SCHEMA;
  END IF;

  IF TG_TABLE_NAME = 'orders' THEN
    NEW.updated_by := current_user;   -- 只有订单表记录操作者
  END IF;

  RETURN NEW;
END;
$$;

通用函数 vs 专用函数

官方在过渡表示例里明确提示：「In practice, it might be better to use three separate functions and avoid the run-time tests on TG_OP.」 通用函数省了代码，但每次调用都要做分支判断；如果分支复杂或性能敏感，拆成三个函数反而更清晰。

波动性：为什么触发器函数必须 VOLATILE ​

CREATE FUNCTION 的默认波动性就是 VOLATILE。触发器函数执行 DML、依赖当前事务状态，必须保持这个默认值。官方原文：

It is inappropriate for AFTER triggers that wish to query rows modified by the current command.

被标为 STABLE 或 IMMUTABLE 后，函数内的查询可能被快照隔离或提前求值，看不到调用语句自身的修改：

sql
-- 错误！AFTER 触发器里读不到本语句写入的行
CREATE OR REPLACE FUNCTION bad_after() RETURNS trigger
LANGUAGE plpgsql STABLE AS $$
DECLARE
  v_cnt int;
BEGIN
  SELECT count(*) INTO v_cnt FROM t;   -- STABLE → 看不到本语句的改动
  RETURN NULL;
END;
$$;
sql
-- 正确：保持默认 VOLATILE
CREATE OR REPLACE FUNCTION good_after() RETURNS trigger
LANGUAGE plpgsql AS $$
DECLARE
  v_cnt int;
BEGIN
  SELECT count(*) INTO v_cnt FROM t;   -- VOLATILE → 看得到
  RETURN NULL;
END;
$$;

同理：任何有副作用的函数都应标 VOLATILE，即使结果可预测，以防调用被优化掉。PARALLEL UNSAFE 是默认值，触发器函数会修改数据库状态，保持默认即可。

SECURITY DEFINER 与权限模型 ​

触发器调用的权限检查分两层：

  • 建触发器需要表上的 TRIGGER 权限；
  • 调用函数需要函数上的 EXECUTE 权限。

触发器执行时，默认以「排队事件的角色」运行，即执行 DML 的那个用户。若希望函数以固定身份运行（例如只让审计表可写、不让普通用户直接写），应声明 SECURITY DEFINER：

sql
CREATE OR REPLACE FUNCTION audit_row_sd() RETURNS trigger
LANGUAGE plpgsql
SECURITY DEFINER                 -- 以函数属主身份执行
SET search_path = public, pg_temp  -- 锁定 search_path，防止劫持 ★
AS $$
BEGIN
  INSERT INTO audit_log(op, changed_at, payload)
  VALUES (TG_OP, now(), to_jsonb(NEW));
  RETURN NULL;
END;
$$;

-- 收紧权限：普通用户不能直接写审计表
REVOKE INSERT ON audit_log FROM PUBLIC;

SECURITY DEFINER 必须锁 search_path

不锁 search_path 时，攻击者可以创建一个同名对象并抢先解析，从而以函数属主身份执行恶意代码。始终显式设置 SET search_path = ...，且把 pg_temp 放在最后。

防递归：pg_trigger_depth() ​

触发器里再对同一张表做 DML，会再次触发触发器，形成级联甚至无限递归。官方指出级联触发器没有层级上限，需要程序员自行避免。用 pg_trigger_depth() 可以判断当前嵌套层级：

sql
CREATE OR REPLACE FUNCTION safer_touch() RETURNS trigger
LANGUAGE plpgsql AS $$
BEGIN
  -- 只在最外层触发时执行，避免自触发递归
  IF pg_trigger_depth() > 1 THEN
    RETURN NULL;
  END IF;

  UPDATE other_table SET touched = true WHERE id = NEW.id;
  RETURN NEW;
END;
$$;

pg_trigger_depth() 返回当前触发器嵌套层级（不在触发器内调用时为 0）。另一种防重入手段是用会话级变量或临时表做标记。

text
用户 UPDATE t
  └─ 触发器 A 触发（depth = 1）
       └─ 函数内 UPDATE t
            └─ 触发器 A 再次触发（depth = 2）  ← 若不加保护会无限递归

不能直接调用：如何调试触发器函数 ​

RETURNS trigger 的函数无法被普通 SQL 调用：

sql
SELECT emp_stamp();
text
ERROR:  trigger functions can only be called as triggers
CONTEXT:  compilation of PL/pgSQL function "emp_stamp" near line 1

SQLSTATE 为 0A000（feature_not_supported）。调试办法：

  • 在函数体里用 RAISE NOTICE / RAISE WARNING 打印中间值；
  • 在事务内执行 DML 后 SELECT 检查结果，再 ROLLBACK：
sql
BEGIN;
UPDATE products SET price = 100 WHERE id = 1;
SELECT * FROM audit_log ORDER BY id DESC LIMIT 5;
ROLLBACK;
  • 用 pg_get_functiondef(func_oid) 反编译出当前函数定义，确认部署版本与预期一致：
sql
SELECT pg_get_functiondef('emp_stamp()'::regprocedure);
SELECT pg_get_triggerdef(oid) FROM pg_trigger WHERE tgname = 'emp_stamp';

AFTER 触发器抛错即回滚 ​

AFTER 触发器无法改值、无法跳行，唯一干预方式是抛错——那会让整个语句连同触发器自身的副作用一起回滚：

sql
CREATE OR REPLACE FUNCTION check_running_total() RETURNS trigger
LANGUAGE plpgsql AS $$
DECLARE
  v_total numeric;
BEGIN
  SELECT sum(amount) INTO v_total
    FROM payment WHERE account_id = NEW.account_id;

  IF v_total > 1000000 THEN
    RAISE EXCEPTION '账户 % 累计付款 % 超限', NEW.account_id, v_total
      USING ERRCODE = '23514';
  END IF;
  RETURN NULL;
END;
$$;

触发器与原语句在同一事务内，任一出错则两者的效果都回滚：

text
BEGIN;
  INSERT INTO payment ...;   -- 成功
  → 触发器抛错
ROLLBACK;                    -- 上面的 INSERT 一并撤销

RAISE 携带诊断信息 ​

抛错时带上 COLUMN / CONSTRAINT / TABLE 等选项，能让 PHP 侧精确定位字段：

sql
RAISE EXCEPTION '数量必须为正: %', NEW.qty
  USING ERRCODE  = '23514',          -- check_violation
        COLUMN   = 'qty',
        TABLE    = TG_TABLE_NAME,
        CONSTRAINT = 'qty_positive';

USING 支持的选项包括 MESSAGE、DETAIL、HINT、ERRCODE、COLUMN、CONSTRAINT、DATATYPE、TABLE、SCHEMA。注意消息占位符只有 % 与 %%，没有 %s：

sql
RAISE NOTICE '表 % 上触发 %', TG_TABLE_NAME, TG_NAME;
RAISE NOTICE '折扣被截断为 50%%';   -- %% 输出一个字面 %

用内置触发器函数 ​

官方提供几个现成的触发器函数（Table 9.110），不必自己写：

函数用途
suppress_redundant_updates_trigger()行级 BEFORE UPDATE，抑制无实际数据变化的更新
tsvector_update_trigger()从纯文本列自动更新 tsvector 列
tsvector_update_trigger_column()同上，但分词配置来自 regconfig 列
sql
-- 抑制「值没变」的伪更新（减少 WAL 与索引维护）
CREATE TRIGGER z_suppress_redundant
  BEFORE UPDATE ON products
  FOR EACH ROW EXECUTE FUNCTION suppress_redundant_updates_trigger();

-- 自动维护全文检索列
CREATE TRIGGER tsvectorupdate
  BEFORE INSERT OR UPDATE ON articles
  FOR EACH ROW EXECUTE FUNCTION
    tsvector_update_trigger(tsv, 'pg_catalog.simple', title, body);

触发器按名称字母序执行

同一张表上、同一事件、同时机的多个触发器，按触发器名称的字母序依次触发。官方建议把抑制冗余更新的触发器命名为 z_ 前缀，使其排在最后执行。

编写模板汇总 ​

sql
-- 模板一：BEFORE 行级，校验 + 补值 + 戳记
CREATE OR REPLACE FUNCTION trg_before_row() RETURNS trigger
LANGUAGE plpgsql AS $$
BEGIN
  IF NEW.amount < 0 THEN
    RAISE EXCEPTION '金额不能为负: %', NEW.amount USING ERRCODE = '23514';
  END IF;
  NEW.updated_at := now();
  RETURN NEW;
END;
$$;

-- 模板二：AFTER 行级，审计 + 联动（固定 RETURN NULL）
CREATE OR REPLACE FUNCTION trg_after_row() RETURNS trigger
LANGUAGE plpgsql AS $$
BEGIN
  INSERT INTO audit_log(op, at, payload)
  VALUES (TG_OP, now(),
          CASE WHEN TG_OP = 'DELETE' THEN to_jsonb(OLD) ELSE to_jsonb(NEW) END);
  RETURN NULL;
END;
$$;

-- 模板三：语句级 + 过渡表，批量处理（固定 RETURN NULL）
CREATE OR REPLACE FUNCTION trg_after_stmt() RETURNS trigger
LANGUAGE plpgsql AS $$
BEGIN
  INSERT INTO audit_log(op, at, payload)
  SELECT 'U', now(), to_jsonb(n) FROM new_rows n;
  RETURN NULL;
END;
$$;

实战示例 ​

场景一：通用审计函数（三事件共用） ​

需求：把 emp 表的增删改逐行写入 emp_audit，记录操作类型、时间、用户与整行快照。

sql
CREATE TABLE emp (
  empname text NOT NULL,
  salary  integer
);

CREATE TABLE emp_audit (
  operation char(1)    NOT NULL,
  stamp     timestamp  NOT NULL,
  userid    text       NOT NULL,
  empname   text       NOT NULL,
  salary    integer
);

CREATE OR REPLACE FUNCTION process_emp_audit() RETURNS trigger
LANGUAGE plpgsql AS $$
BEGIN
  IF TG_OP = 'DELETE' THEN
    INSERT INTO emp_audit SELECT 'D', now(), current_user, OLD.*;
  ELSIF TG_OP = 'UPDATE' THEN
    INSERT INTO emp_audit SELECT 'U', now(), current_user, NEW.*;
  ELSE
    INSERT INTO emp_audit SELECT 'I', now(), current_user, NEW.*;
  END IF;
  RETURN NULL;   -- AFTER 触发器，返回值被忽略
END;
$$;

CREATE TRIGGER emp_audit
  AFTER INSERT OR UPDATE OR DELETE ON emp
  FOR EACH ROW EXECUTE FUNCTION process_emp_audit();

运行验证：

sql
INSERT INTO emp VALUES ('alice', 40000);
UPDATE emp SET salary = 45000 WHERE empname = 'alice';
DELETE FROM emp WHERE empname = 'alice';
SELECT operation, userid, empname, salary FROM emp_audit ORDER BY stamp;
text
 operation | userid | empname | salary
-----------+--------+---------+--------
 I         | ch     | alice   |  40000
 U         | ch     | alice   |  45000
 D         | ch     | alice   |  45000
(3 rows)

场景二：配置驱动的列戳记（TG_ARGV） ​

需求：同一个函数，通过 TG_ARGV 指定要戳记的列，绑定到多张表。

sql
CREATE TABLE articles (
  id         bigserial PRIMARY KEY,
  title      text,
  created_at timestamptz,
  updated_at timestamptz
);

CREATE OR REPLACE FUNCTION stamp_col() RETURNS trigger
LANGUAGE plpgsql AS $$
BEGIN
  IF TG_NARGS <> 1 THEN
    RAISE EXCEPTION '触发器 % 需要一个列名参数', TG_NAME;
  END IF;

  IF TG_ARGV[0] = 'created_at' THEN
    NEW.created_at := COALESCE(NEW.created_at, now());
  ELSIF TG_ARGV[0] = 'updated_at' THEN
    NEW.updated_at := now();
  ELSE
    RAISE EXCEPTION '不支持的列: %', TG_ARGV[0] USING ERRCODE = '22023';
  END IF;

  RETURN NEW;
END;
$$;

CREATE TRIGGER trg_articles_created
  BEFORE INSERT ON articles
  FOR EACH ROW EXECUTE FUNCTION stamp_col('created_at');

CREATE TRIGGER trg_articles_updated
  BEFORE INSERT OR UPDATE ON articles
  FOR EACH ROW EXECUTE FUNCTION stamp_col('updated_at');
sql
INSERT INTO articles (title) VALUES ('PostgreSQL 触发器入门');
SELECT id, title, created_at IS NOT NULL AS has_created,
       updated_at IS NOT NULL AS has_updated
FROM articles;
text
 id |          title           | has_created | has_updated
----+--------------------------+-------------+-------------
  1 | PostgreSQL 触发器入门    | t           | t
(1 row)

场景三：状态机流转校验 ​

需求：只允许 draft → published → archived 的单向流转，其他跃迁一律报错并给出可定位的诊断。

sql
CREATE TABLE docs (
  id     bigserial PRIMARY KEY,
  status text NOT NULL DEFAULT 'draft',
  title  text NOT NULL
);

CREATE OR REPLACE FUNCTION check_status_transition() RETURNS trigger
LANGUAGE plpgsql AS $$
BEGIN
  -- 空值保护：状态不可置空
  IF NEW.status IS NULL THEN
    RAISE EXCEPTION 'status 不能为空'
      USING ERRCODE = '23502', COLUMN = 'status', TABLE = TG_TABLE_NAME;
  END IF;

  -- 无变化的更新直接放行
  IF NEW.status = OLD.status THEN
    RETURN NEW;
  END IF;

  IF NOT (
       (OLD.status = 'draft'     AND NEW.status = 'published')
    OR (OLD.status = 'published' AND NEW.status = 'archived')
  ) THEN
    RAISE EXCEPTION '非法状态流转: % -> %', OLD.status, NEW.status
      USING ERRCODE    = '23514',
            COLUMN     = 'status',
            TABLE      = TG_TABLE_NAME,
            CONSTRAINT = 'valid_status_transition';
  END IF;

  RETURN NEW;
END;
$$;

CREATE TRIGGER trg_docs_status
  BEFORE UPDATE OF status ON docs
  FOR EACH ROW EXECUTE FUNCTION check_status_transition();
sql
INSERT INTO docs (title) VALUES ('触发器设计');
UPDATE docs SET status = 'published' WHERE id = 1;   -- 允许
UPDATE docs SET status = 'draft' WHERE id = 1;       -- 拒绝
text
UPDATE 1
ERROR:  非法状态流转: published -> draft
DETAIL:  ...
CONTEXT:  PL/pgSQL function check_status_transition() line 15 at RAISE

场景四：汇总表维护（幂等 + 异常兜底） ​

需求：维护按维度汇总的表，用「先 UPDATE，无命中则 INSERT，竞态时容忍唯一冲突」的幂等模式。这是官方汇总表示例的核心手法：

sql
CREATE TABLE sales_summary (
  time_key     integer PRIMARY KEY,
  amount_sold  numeric(15,2) NOT NULL,
  units_sold   numeric(12)   NOT NULL
);

CREATE OR REPLACE FUNCTION maint_summary() RETURNS trigger
LANGUAGE plpgsql AS $$
DECLARE
  d_time   integer;
  d_amount numeric(15,2);
  d_units  numeric(12);
BEGIN
  IF TG_OP = 'DELETE' THEN
    d_time := OLD.time_key;
    d_amount := -1 * OLD.amount_sold;
    d_units  := -1 * OLD.units_sold;
  ELSIF TG_OP = 'UPDATE' THEN
    IF OLD.time_key <> NEW.time_key THEN
      RAISE EXCEPTION '不允许修改 time_key: % -> %', OLD.time_key, NEW.time_key;
    END IF;
    d_time   := OLD.time_key;
    d_amount := NEW.amount_sold - OLD.amount_sold;
    d_units  := NEW.units_sold  - OLD.units_sold;
  ELSE
    d_time   := NEW.time_key;
    d_amount := NEW.amount_sold;
    d_units  := NEW.units_sold;
  END IF;

  <<insert_update>>
  LOOP
    UPDATE sales_summary
       SET amount_sold = amount_sold + d_amount,
           units_sold  = units_sold  + d_units
     WHERE time_key = d_time;

    EXIT insert_update WHEN found;

    BEGIN
      INSERT INTO sales_summary (time_key, amount_sold, units_sold)
      VALUES (d_time, d_amount, d_units);
      EXIT insert_update;
    EXCEPTION
      WHEN UNIQUE_VIOLATION THEN
        -- 并发下另一事务刚插入同一行：回到循环重试 UPDATE
        NULL;
    END;
  END LOOP insert_update;

  RETURN NULL;   -- AFTER 触发器
END;
$$;

CREATE TRIGGER maint_summary
  AFTER INSERT OR UPDATE OR DELETE ON sales_fact
  FOR EACH ROW EXECUTE FUNCTION maint_summary();
sql
INSERT INTO sales_fact VALUES (1, 1, 10, 3);
INSERT INTO sales_fact VALUES (1, 2, 20, 5);
SELECT time_key, amount_sold, units_sold FROM sales_summary;
text
 time_key | amount_sold | units_sold
----------+-------------+------------
        1 |       30.00 |          8
(1 row)

这个模式的三个要点：增量而非全量重算、<<insert_update>> 循环配合 EXIT ... WHEN found、EXCEPTION WHEN UNIQUE_VIOLATION 兜底并发。

场景五：防递归的层级校验 ​

需求：employees.manager_id 指向本表，禁止形成环路。插入/更新时向上回溯祖先链，同时用 pg_trigger_depth() 防止自触发递归。

sql
CREATE TABLE employees (
  id         bigserial PRIMARY KEY,
  name       text NOT NULL,
  manager_id bigint REFERENCES employees(id)
);

CREATE OR REPLACE FUNCTION check_no_cycle() RETURNS trigger
LANGUAGE plpgsql AS $$
DECLARE
  v_cur  bigint;
  v_hops int := 0;
BEGIN
  IF NEW.manager_id IS NULL THEN
    RETURN NEW;
  END IF;

  -- 直接自指
  IF NEW.manager_id = NEW.id THEN
    RAISE EXCEPTION '员工不能是自己的上级' USING ERRCODE = '23514';
  END IF;

  -- 向上回溯，检测环路；上限兜底，避免脏数据导致死循环
  v_cur := NEW.manager_id;
  WHILE v_cur IS NOT NULL AND v_hops < 1000 LOOP
    IF v_cur = NEW.id THEN
      RAISE EXCEPTION '检测到管理关系环路: id=%', NEW.id USING ERRCODE = '23514';
    END IF;

    SELECT manager_id INTO v_cur FROM employees WHERE id = v_cur;
    v_hops := v_hops + 1;
  END LOOP;

  RETURN NEW;
END;
$$;

CREATE TRIGGER trg_no_cycle
  BEFORE INSERT OR UPDATE OF manager_id ON employees
  FOR EACH ROW
  WHEN (pg_trigger_depth() < 5)     -- 兜底：嵌套过深直接跳过
  EXECUTE FUNCTION check_no_cycle();
sql
INSERT INTO employees (name, manager_id) VALUES ('CEO', NULL);
INSERT INTO employees (name, manager_id) VALUES ('CTO', 1);
INSERT INTO employees (name, manager_id) VALUES ('DEV', 2);
UPDATE employees SET manager_id = 3 WHERE id = 1;    -- 形成环，拒绝
text
INSERT 0 1
INSERT 0 1
INSERT 0 1
ERROR:  检测到管理关系环路: id=1

递归触发的兜底不可省

WHILE 里的 v_hops < 1000 与 WHEN (pg_trigger_depth() < 5) 都属于防御性兜底。即便业务逻辑上看不到环路，也要防止脏数据或并发写入导致回溯永不终止。

注意事项 ​

  • 给触发器函数加形参：CREATE TRIGGER 的参数走 TG_ARGV，函数签名必须为空。-- 错误！
    sql
    CREATE FUNCTION f(a text) RETURNS trigger ...;   -- 建触发器时会失败
  • 返回类型不是 trigger：数据变更触发器只接受 trigger。-- 错误！
    sql
    CREATE FUNCTION f() RETURNS void ...
  • BEFORE 行级忘记 RETURN：函数末尾无 RETURN 时返回 NULL，所有行被静默跳过。-- 错误！
    sql
    BEGIN
  NEW.updated_at := now();
END;                        -- 缺 RETURN NEW; → UPDATE 0
  • 在 AFTER 行级里改 NEW 并期望生效：返回值被完全忽略。-- 不推荐
  • 在 DELETE 触发器里返回 NEW：NEW 为 NULL，会被当成「跳过」。-- 错误！
    sql
    RETURN NEW;                 -- DELETE 中 NEW 是 NULL → 该行不删除
  • 把触发器函数声明为 STABLE / IMMUTABLE：看不到调用语句的修改。-- 错误！
  • 直接 SELECT 调用触发器函数：ERROR: trigger functions can only be called as triggers（0A000）。-- 错误！
  • SECURITY DEFINER 不锁 search_path：存在权限提升风险。-- 错误！
    sql
    CREATE FUNCTION f() RETURNS trigger SECURITY DEFINER AS $$ ... $$;  -- 缺 SET search_path
  • 在触发器里对同表做 DML 且无递归保护：会级联触发甚至无限递归。-- 错误！
  • 用 %s 做占位符：PL/pgSQL 的 RAISE 只认 % 与 %%。-- 错误！
    sql
    RAISE NOTICE '%s', TG_TABLE_NAME;    -- 会把 %s 原样输出
  • 依赖触发器的执行顺序做业务：只按名称字母序，且可变。-- 不推荐
  • 在源码里动态拼接 TG_ARGV 当列名：必须走白名单 + format(... %I)。-- 错误！
错误写法报错 / 后果正确做法
RETURNS voidCREATE TRIGGER 失败RETURNS trigger
CREATE FUNCTION f(a text)签名不匹配零参数 + TG_ARGV
BEFORE 末尾无 RETURN静默跳过全部行RETURN NEW（DELETE 用 OLD）
AFTER 中改 NEW无效果只能改「将要写入的行」在 BEFORE 里做
SELECT f()0A000通过 DML 间接触发
SECURITY DEFINER 无 SET search_path安全风险显式锁定 search_path
同表 DML 无递归保护级联 / 无限递归pg_trigger_depth() / 标记
RAISE ... '%s'占位符不生效用 %

最佳实践 ​

  • 函数名与触发器名都带语义前缀：trg_<表>_<用途>，便于在 pg_trigger 中检索。
  • 行级函数结尾固定 IF TG_OP = 'DELETE' THEN RETURN OLD; END IF; RETURN NEW;，作为团队强制模板。
  • 语句级与 AFTER 行级统一 RETURN NULL，并在注释里写明「返回值被忽略」。
  • 校验写 BEFORE，联动写 AFTER：BEFORE 只能保证本行此刻合法，跨行/跨表要看最终值。
    sql
    CREATE TRIGGER trg_v BEFORE INSERT ON t FOR EACH ROW EXECUTE FUNCTION validate();
CREATE TRIGGER trg_a AFTER INSERT ON t FOR EACH ROW EXECUTE FUNCTION propagate();
  • AFTER 行级一律配 WHEN：条件不满足时连事件都不排队，大批量 DML 下收益显著。
    sql
    WHEN (OLD.price IS DISTINCT FROM NEW.price)
  • 保持默认 VOLATILE，不要给触发器函数加 STABLE / IMMUTABLE。
  • SECURITY DEFINER 必配 SET search_path，并 REVOKE 底层表的直接写权限。
  • 抛错一律带 ERRCODE + COLUMN / CONSTRAINT / TABLE，让应用侧能定位字段。
    sql
    RAISE EXCEPTION '数量必须为正: %', NEW.qty
  USING ERRCODE = '23514', COLUMN = 'qty', TABLE = TG_TABLE_NAME;
  • 重活留给语句级 + 过渡表，行级函数体必须廉价。
  • 同一函数服务多表时，优先用显式分支而非动态 SQL；确需动态时用白名单 + %I。
  • 用 pg_trigger_depth() 或标记表防递归，并给回溯/循环加操作次数上限。
  • 用 COMMENT ON FUNCTION / COMMENT ON TRIGGER 文档化，注明时机、事件、改写的列：
    sql
    COMMENT ON FUNCTION check_status_transition() IS '状态机：只允许 draft→published→archived';
COMMENT ON TRIGGER trg_docs_status ON docs
  IS 'BEFORE UPDATE OF status：校验状态流转合法性';
  • 用 pg_get_functiondef() / pg_get_triggerdef() 做部署比对，确认线上版本与预期一致。
    sql
    SELECT pg_get_functiondef('check_status_transition()'::regprocedure);
SELECT pg_get_triggerdef(oid) FROM pg_trigger WHERE tgname = 'trg_docs_status';
  • 能用约束表达的不要用触发器：CHECK、唯一索引、外键、生成列都比触发器廉价且声明式。
  • 优先用内置触发器函数（suppress_redundant_updates_trigger、tsvector_update_trigger），避免重复造轮子。
场景推荐反例
逐行改写 / 校验BEFORE 行级 + RETURN NEWAFTER 里改 NEW
逐行审计AFTER 行级 + RETURN NULL试图用返回值阻止操作
批量联动语句级 + 过渡表行级里逐行 UPDATE 汇总表
参数化行为TG_ARGV + 白名单分支动态拼列名
多表复用TG_TABLE_NAME 分支每张表复制一份函数（可选，视复杂度）
防递归pg_trigger_depth() / 标记无保护地对同表 DML
权限隔离SECURITY DEFINER + SET search_path直接 SECURITY DEFINER

下一节

下一节我们学习：事件触发器概述，了解如何把触发器机制从「表上的 DML」扩展到「整个数据库的 DDL 命令」，以及事件触发器函数与普通触发器函数在签名、返回值与可用变量上的差异。

参考链接 ​