PHP小码农

Appearance

行级触发器 FOR EACH ROW ​

FOR EACH ROW 让触发器函数对影响到的每一行各执行一次，并在函数里通过 NEW / OLD 拿到这一行的新值与旧值。它是实现「逐行校验、逐行改写、逐行审计」的基础，也是触发器性能问题的主要来源——影响 100 万行的语句意味着 100 万次函数调用。本节讲清楚行级触发器的变量语义、返回值规则、数据可见性与性能取舍。

前置知识

阅读本节前，建议先了解：触发事件：INSERT / UPDATE / DELETE / TRUNCATE

基础概念 ​

行级与语句级的执行次数差异 ​

text
UPDATE t SET v = v + 1 WHERE 条件命中 3 行;

FOR EACH ROW        → 函数执行 3 次，每次 NEW/OLD 是「当前这一行」   ★
FOR EACH STATEMENT  → 函数执行 1 次，NEW/OLD 均为 NULL

UPDATE t SET v = v + 1 WHERE 条件命中 0 行;

FOR EACH ROW        → 函数执行 0 次
FOR EACH STATEMENT  → 函数仍然执行 1 次

要点：行级触发器在零行受影响时不执行，语句级触发器则照常执行。这个差异会直接影响「记录一次批量操作」这类需求应该选哪种层级。

NEW / OLD 的完整语义 ​

变量类型何时有值可否赋值
NEWrecord行级 INSERT / UPDATE仅 BEFORE 可赋值；AFTER 中赋值无效
OLDrecord行级 UPDATE / DELETE始终不可赋值
text
行级 INSERT：  OLD = 未赋值（访问报错）    NEW = 待插入的行（BEFORE 可写）
行级 UPDATE：  OLD = 更新前的行（只读）    NEW = 待写入的行（BEFORE 可写）
行级 DELETE：  OLD = 被删除的行（只读）    NEW = 未赋值（访问报错）

两端变量的字段与实际表结构一一对应，可以直接按列名访问：

sql
CREATE OR REPLACE FUNCTION demo_vars() RETURNS trigger
LANGUAGE plpgsql AS $$
BEGIN
  RAISE NOTICE 'WHEN=% LEVEL=% OP=% TABLE=%',
    TG_WHEN, TG_LEVEL, TG_OP, TG_TABLE_NAME;

  IF TG_OP = 'UPDATE' THEN
    RAISE NOTICE '旧值 % → 新值 %', OLD.price, NEW.price;
  END IF;

  IF TG_OP <> 'DELETE' THEN
    NEW.updated_at := now();
  END IF;

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

返回值规则（行级专属） ​

时机返回值的作用返回 NULL 的效果
BEFORE（INSERT/UPDATE）作为该行最终写入值跳过该行，后续同行触发器不再执行
BEFORE（DELETE）无直接影响，但必须非 NULL跳过该行删除
AFTER（全部）完全被忽略无影响
sql
-- 最常见的模板：DELETE 返回 OLD，其余返回 NEW
IF TG_OP = 'DELETE' THEN
  RETURN OLD;
END IF;
RETURN NEW;

忘记 RETURN 的后果

PL/pgSQL 函数走到结尾没有 RETURN 时返回 NULL。对 BEFORE 行级触发器而言，这等价于静默跳过所有行——UPDATE 0 而应用毫无察觉。

语法与用法 ​

行级触发器的定义 ​

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

FOR EACH ROW 也可简写为 FOR ROW。TRUNCATE 事件不能与 FOR EACH ROW 组合。

典型的三段式结构 ​

sql
CREATE OR REPLACE FUNCTION trg_<用途>() RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
  -- ① 前置判断（也可交给 WHEN 条件）
  -- ② 业务逻辑（可读写 NEW，可查其他表）
  -- ③ 返回：BEFORE 必返 NEW/OLD，AFTER 返回 NULL 即可
  IF TG_OP = 'DELETE' THEN
    RETURN OLD;
  END IF;
  RETURN NEW;
END;
$$;

CREATE TRIGGER trg_<用途>
  BEFORE INSERT OR UPDATE ON <表>
  FOR EACH ROW EXECUTE FUNCTION trg_<用途>();

一个最小的完整闭环 ​

sql
CREATE TABLE ticket (
  id         bigserial PRIMARY KEY,
  subject    text NOT NULL,
  status     text NOT NULL DEFAULT 'open',
  updated_at timestamptz
);

CREATE OR REPLACE FUNCTION ticket_touch() RETURNS trigger
LANGUAGE plpgsql AS $$
BEGIN
  NEW.updated_at := now();
  RETURN NEW;                 -- 必须返回
END;
$$;

CREATE TRIGGER trg_ticket_touch
  BEFORE INSERT OR UPDATE ON ticket
  FOR EACH ROW EXECUTE FUNCTION ticket_touch();

INSERT INTO ticket (subject) VALUES ('登录异常') RETURNING id, subject, updated_at;

详细说明 ​

BEFORE 行级：改写即将写入的行 ​

BEFORE 行级最常见的三类用途：

sql
-- ① 补值
NEW.created_at := COALESCE(NEW.created_at, now());

-- ② 规范化
NEW.email := lower(trim(NEW.email));
NEW.phone := regexp_replace(NEW.phone, '\D', '', 'g');

-- ③ 计算派生值
NEW.full_name := NEW.last_name || NEW.first_name;

也可以做「条件式改写」：

sql
CREATE OR REPLACE FUNCTION clamp_discount() RETURNS trigger
LANGUAGE plpgsql AS $$
BEGIN
  IF NEW.discount > 0.5 THEN
    NEW.discount := 0.5;                 -- 静默修正（也可改成抛错）
    RAISE WARNING '折扣被截断为 50%%';
  END IF;
  RETURN NEW;
END;
$$;

改值与抛错如何选择

静默修正会造成「写进去的和传进来的不一样」，调用方无从知晓；抛错则要求调用方处理。业务上一般原则是：数据可安全归一化的静默修正，涉及金额/权限的一律抛错。

BEFORE 行级：用 NULL 跳过某一行 ​

返回 NULL 是「有条件忽略写入」的官方机制：

sql
CREATE OR REPLACE FUNCTION skip_test_rows() RETURNS trigger
LANGUAGE plpgsql AS $$
BEGIN
  IF NEW.email LIKE '%@example.invalid' THEN
    RAISE NOTICE '跳过测试地址: %', NEW.email;
    RETURN NULL;                        -- 本行不写入
  END IF;
  RETURN NEW;
END;
$$;

CREATE TRIGGER trg_skip_test_rows
  BEFORE INSERT ON leads
  FOR EACH ROW EXECUTE FUNCTION skip_test_rows();

要点：

  • 返回 NULL 后，同一行上后续的同事件 BEFORE 触发器不再执行；
  • AFTER 触发器也不会为该行执行（因为该行根本没写入）；
  • 被跳过的行不计入 INSERT 0 N 的行数。
text
INSERT INTO leads (email) VALUES ('a@real.com'), ('b@example.invalid'), ('c@real.com');
NOTICE:  跳过测试地址: b@example.invalid
INSERT 0 2

行级 AFTER：在语句末尾逐行回调 ​

AFTER 行级触发器虽然「每行一次」，但执行时刻是整个语句处理完所有行之后：

text
语句开始
  ├─ 行 1 BEFORE → 写行 1
  ├─ 行 2 BEFORE → 写行 2
  └─ 行 3 BEFORE → 写行 3
语句的行处理结束
  ├─ 行 1 AFTER
  ├─ 行 2 AFTER
  └─ 行 3 AFTER
语句级 AFTER

因此在 AFTER 行级触发器里 SELECT 目标表，可以看到本条语句影响的所有行：

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;                          -- AFTER 返回值被忽略
END;
$$;

AFTER 行级触发器不能阻止操作，唯一的干预方式是抛异常——那会让整个语句连同触发器自身的副作用一起回滚。

数据可见性规则 ​

官方对触发器内 SQL 能看到什么有明确定义：

触发器类型它执行的 SQL 能看到什么
语句级 BEFORE看不到本语句的任何修改
语句级 AFTER看到本语句的全部修改
行级 BEFORE看不到本行尚未发生的修改，但能看到同一外层命令中此前已处理行的修改
行级 INSTEAD OF能看到同一外层命令中此前 INSTEAD OF 触发产生的修改
行级 AFTER外层命令的全部修改都已完成且可见

行级 BEFORE 的可见性尤其容易踩坑：行的处理顺序不可预期，所以「在 BEFORE 触发器里统计当前已写入的行数」会得到不确定的结果。

sql
-- 不推荐：行级 BEFORE 里依赖「已处理行数」，结果不确定
CREATE OR REPLACE FUNCTION unstable_counter() RETURNS trigger
LANGUAGE plpgsql AS $$
DECLARE
  v_cnt int;
BEGIN
  SELECT count(*) INTO v_cnt FROM counter_table;   -- 行序不定 → 结果不定
  NEW.seq := v_cnt + 1;
  RETURN NEW;
END;
$$;

函数易变性会影响可见性

上述可见性规则只在触发器函数声明为 VOLATILE（默认）时成立。声明为 STABLE 或 IMMUTABLE 的函数看不到调用语句造成的修改。

VOLATILE / STABLE / IMMUTABLE 与触发器 ​

触发器函数会执行 DML、依赖当前事务状态，因此应当保持默认的 VOLATILE：

sql
-- 正确：默认 VOLATILE
CREATE FUNCTION f() RETURNS trigger LANGUAGE plpgsql AS $$ ... $$;
sql
-- 危险：数据库允许这样声明，但语义会出错
CREATE FUNCTION f() RETURNS trigger LANGUAGE plpgsql STABLE AS $$ ... $$;   -- 不推荐

被标为 STABLE/IMMUTABLE 后，函数内的查询可能被快照隔离或提前求值，导致看不到本语句刚刚写入的行。

生成列在行级触发器中的可见性 ​

存储生成列的计算发生在 BEFORE 之后、AFTER 之前，因此：

sql
CREATE TABLE invoice (
  qty      int NOT NULL,
  price    numeric(10,2) NOT NULL,
  amount   numeric(12,2) GENERATED ALWAYS AS (qty * price) STORED
);

-- BEFORE：NEW.amount 尚未计算，不可访问
CREATE OR REPLACE FUNCTION before_read_gen() RETURNS trigger
LANGUAGE plpgsql AS $$
BEGIN
  -- 不推荐：值是未定义的
  -- RAISE NOTICE '%', NEW.amount;
  RETURN NEW;
END;
$$;

-- AFTER：NEW.amount 已是最终值，可以放心读
CREATE OR REPLACE FUNCTION after_read_gen() RETURNS trigger
LANGUAGE plpgsql AS $$
BEGIN
  IF NEW.amount > 100000 THEN
    RAISE WARNING '单据金额异常: %', NEW.amount;
  END IF;
  RETURN NULL;
END;
$$;

在 BEFORE 触发器里对生成列赋值会被忽略并覆盖：

sql
NEW.amount := 0;      -- 无效，随后会被生成列计算覆盖

INSTEAD OF 也是行级 ​

INSTEAD OF 触发器必须是行级（视图上唯一允许的行级触发器），其返回值语义与普通 BEFORE 不同：

返回含义
NULL「我没有修改任何底层数据」→ 该行不计入影响行数，后续同行触发器不执行
非 NULL（NEW/OLD）「操作已完成」→ 影响行数 +1，并决定 RETURNING 的输出
sql
-- DELETE 分支：没删到任何行时返回 NULL，让影响行数如实为 0
DELETE FROM emp WHERE empname = OLD.empname;
IF NOT FOUND THEN
  RETURN NULL;
END IF;
RETURN OLD;

行级触发器的性能计量 ​

一次 UPDATE 影响 N 行、表上有 M 个行级触发器时：

text
函数调用次数 = N × M
外加：每行的 NEW/OLD 构造、WHEN 条件求值、记录变量的内存分配

粗算示例：

行数 N行级触发器 M函数调用次数
1002200
10,000220,000
1,000,00022,000,000

因此有两条硬性建议：

  • 行级触发器函数体必须保持廉价，禁止在其中做全表扫描、HTTP 调用、pg_sleep；
  • 大批量 DML 的场景优先考虑语句级 + 过渡表。
sql
-- 用 WHEN 在排队阶段就跳过无意义的行
CREATE TRIGGER trg_audit
  AFTER UPDATE ON products
  FOR EACH ROW
  WHEN (OLD.price IS DISTINCT FROM NEW.price)
  EXECUTE FUNCTION audit_price();

对 AFTER 触发器，WHEN 不满足时连事件都不排队，收益比在函数体里判断大得多。

行级触发器的常见误用 ​

sql
-- 误用①：在行级触发器里做逐行计数（应改用语句级 + 过渡表或序列）
CREATE OR REPLACE FUNCTION bad_counter() RETURNS trigger
LANGUAGE plpgsql AS $$
BEGIN
  UPDATE stats SET cnt = cnt + 1 WHERE table_name = TG_TABLE_NAME;   -- 每行一次
  RETURN NULL;
END;
$$;

-- 误用②：把行级触发器当作「乐观锁」使用，却忽略行处理顺序
-- 误用③：在 AFTER 触发器里修改 NEW（无效，且误导后来者）
NEW.v := 1;    -- AFTER 中不生效

分区表与行级触发器 ​

  • 在分区表上创建的行级触发器会被克隆到每个分区；
  • 修改分区表时，受影响分区上的行级触发器会在该分区触发，即使该分区没被显式命名；
  • UPDATE 导致行跨分区移动时，内部按「原分区 DELETE + 目标分区 INSERT」执行：
text
原分区：BEFORE UPDATE 触发器 → BEFORE DELETE 触发器
目标分区：BEFORE INSERT 触发器
AFTER 层：触发 AFTER DELETE 与 AFTER INSERT，不触发 AFTER UPDATE
语句级：只触发 UPDATE 相关触发器

行级触发器的参数传递 ​

触发器函数签名必须零参数，参数通过 CREATE TRIGGER 传给 TG_NARGS / TG_ARGV：

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

更常用、也更安全的做法是把「参数 → 列」的映射写成显式分支，避免动态 SQL：

sql
CREATE OR REPLACE FUNCTION stamp_cols() RETURNS trigger
LANGUAGE plpgsql AS $$
BEGIN
  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;
$$;

CREATE TRIGGER trg_stamp_cols
  BEFORE INSERT OR UPDATE ON articles
  FOR EACH ROW EXECUTE FUNCTION stamp_cols('updated_at');

只有当列名完全来自可信配置（而非用户输入）时才考虑动态写法。此时可以把「列名 → 值」组装成 JSON 补丁，再合并回 NEW：

sql
-- 可信场景：把 TG_ARGV[0] 指定的列设为传入值
NEW := jsonb_populate_record(
         NEW,
         jsonb_build_object(TG_ARGV[0], TG_ARGV[1])
       );

动态组装列名必须用 %I

若必须动态拼 SQL，把 TG_ARGV 的值直接拼进去会带来注入与语法错误风险，务必用 format(... %I) 做标识符引用、用 USING 传值。

实战示例 ​

场景：订单明细的金额校验与自动汇总 ​

需求：

  1. 明细行写入前校验单价与数量为正（BEFORE 行级）；
  2. 明细行写入后自动更新订单头总额（AFTER 行级，需读到本条语句的全部明细）。
sql
CREATE TABLE order_head (
  id          bigserial PRIMARY KEY,
  customer    text NOT NULL,
  total_amount numeric(14,2) NOT NULL DEFAULT 0,
  updated_at  timestamptz
);

CREATE TABLE order_line (
  id        bigserial PRIMARY KEY,
  order_id  bigint NOT NULL REFERENCES order_head(id) ON DELETE CASCADE,
  sku       text NOT NULL,
  qty       int NOT NULL,
  unit_price numeric(12,2) NOT NULL,
  amount    numeric(14,2) GENERATED ALWAYS AS (qty * unit_price) STORED
);

-- BEFORE 行级：逐行校验
CREATE OR REPLACE FUNCTION order_line_validate() RETURNS trigger
LANGUAGE plpgsql AS $$
BEGIN
  IF NEW.qty <= 0 THEN
    RAISE EXCEPTION '明细数量必须为正，当前为 %', NEW.qty
      USING ERRCODE = '23514', COLUMN = 'qty', TABLE = 'order_line';
  END IF;

  IF NEW.unit_price <= 0 THEN
    RAISE EXCEPTION '单价必须为正，当前为 %', NEW.unit_price
      USING ERRCODE = '23514', COLUMN = 'unit_price', TABLE = 'order_line';
  END IF;

  RETURN NEW;
END;
$$;

CREATE TRIGGER trg_order_line_validate
  BEFORE INSERT OR UPDATE OF qty, unit_price ON order_line
  FOR EACH ROW EXECUTE FUNCTION order_line_validate();

-- AFTER 行级：重算订单头（此刻本语句所有明细都已写入）
CREATE OR REPLACE FUNCTION order_head_resync() RETURNS trigger
LANGUAGE plpgsql AS $$
DECLARE
  v_order_id bigint;
  v_total    numeric(14,2);
BEGIN
  v_order_id := COALESCE(NEW.order_id, OLD.order_id);

  SELECT COALESCE(sum(amount), 0) INTO v_total
    FROM order_line WHERE order_id = v_order_id;

  UPDATE order_head
     SET total_amount = v_total,
         updated_at   = now()
   WHERE id = v_order_id;

  RETURN NULL;         -- AFTER 返回值被忽略
END;
$$;

CREATE TRIGGER trg_order_head_resync
  AFTER INSERT OR UPDATE OR DELETE ON order_line
  FOR EACH ROW EXECUTE FUNCTION order_head_resync();
text
INSERT INTO order_head (customer) VALUES ('张三') RETURNING id;
 id
----
  1
(1 row)

INSERT INTO order_line (order_id, sku, qty, unit_price)
VALUES (1, 'SKU-001', 2, 19.90), (1, 'SKU-002', 1, 5.00);
INSERT 0 2

SELECT id, customer, total_amount FROM order_head;
 id | customer | total_amount
----+----------+--------------
  1 | 张三     |        44.80
(1 row)

INSERT INTO order_line (order_id, sku, qty, unit_price) VALUES (1, 'SKU-003', 0, 9.90);
ERROR:  明细数量必须为正，当前为 0
-- 整条语句回滚，order_line 与 order_head 均无变化

UPDATE order_line SET qty = 3 WHERE sku = 'SKU-001';
UPDATE 1

SELECT total_amount FROM order_head;
 total_amount
--------------
        64.70
(1 row)

DELETE FROM order_line WHERE sku = 'SKU-002';
DELETE 1

SELECT total_amount FROM order_head;
 total_amount
--------------
        59.70
(1 row)

注意 GENERATED ALWAYS AS (qty * unit_price) STORED 的 amount 列：BEFORE 触发器里不读它，AFTER 触发器里的 sum(amount) 才可靠。

场景：行级 BEFORE 实现「软删除 + 唯一约束」共存 ​

需求：user_account 表要求 email 唯一，但允许已软删除的账号复用 email。唯一索引无法表达「只对未删除行唯一」，因此用部分唯一索引配合 BEFORE 触发器处理冲突。

sql
CREATE TABLE user_account (
  id         bigserial PRIMARY KEY,
  email      text NOT NULL,
  deleted_at timestamptz,
  version    int NOT NULL DEFAULT 1
);

-- 部分唯一索引：只约束未删除的行
CREATE UNIQUE INDEX uq_user_active_email
  ON user_account (lower(email))
  WHERE deleted_at IS NULL;

-- BEFORE 行级：规范化 email，把软删除行的 email 加上后缀腾出位置
CREATE OR REPLACE FUNCTION user_account_normalize() RETURNS trigger
LANGUAGE plpgsql AS $$
BEGIN
  NEW.email := lower(trim(NEW.email));

  -- 被软删除时，把 email 改写成「原值#id」以释放唯一性
  IF NEW.deleted_at IS NOT NULL AND OLD.deleted_at IS NULL THEN
    NEW.email := NEW.email || '#' || NEW.id::text;
    NEW.version := OLD.version + 1;
  END IF;

  RETURN NEW;
END;
$$;

CREATE TRIGGER trg_user_account_normalize
  BEFORE INSERT OR UPDATE ON user_account
  FOR EACH ROW EXECUTE FUNCTION user_account_normalize();

-- AFTER 行级：记录每次状态流转
CREATE TABLE user_account_log (
  id      bigserial PRIMARY KEY,
  user_id bigint NOT NULL,
  action  text NOT NULL,
  at      timestamptz NOT NULL DEFAULT now()
);

CREATE OR REPLACE FUNCTION user_account_audit() RETURNS trigger
LANGUAGE plpgsql AS $$
BEGIN
  IF TG_OP = 'INSERT' THEN
    INSERT INTO user_account_log (user_id, action) VALUES (NEW.id, 'created');
  ELSIF TG_OP = 'UPDATE' AND OLD.deleted_at IS NULL AND NEW.deleted_at IS NOT NULL THEN
    INSERT INTO user_account_log (user_id, action) VALUES (NEW.id, 'soft_deleted');
  ELSIF TG_OP = 'UPDATE' AND OLD.deleted_at IS NOT NULL AND NEW.deleted_at IS NULL THEN
    INSERT INTO user_account_log (user_id, action) VALUES (NEW.id, 'restored');
  END IF;
  RETURN NULL;
END;
$$;

CREATE TRIGGER trg_user_account_audit
  AFTER INSERT OR UPDATE ON user_account
  FOR EACH ROW EXECUTE FUNCTION user_account_audit();
text
INSERT INTO user_account (email) VALUES ('Alice@Example.COM');
INSERT 0 1

SELECT id, email, deleted_at FROM user_account;
 id |       email        | deleted_at
----+--------------------+------------
  1 | alice@example.com  |
(1 row)

-- 重复插入会被部分唯一索引拦住
INSERT INTO user_account (email) VALUES ('alice@example.com');
ERROR:  duplicate key value violates unique constraint "uq_user_active_email"

-- 软删除后邮箱被释放
UPDATE user_account SET deleted_at = now() WHERE id = 1;
UPDATE 1

SELECT id, email, deleted_at IS NOT NULL AS deleted FROM user_account;
 id |        email        | deleted
----+---------------------+---------
  1 | alice@example.com#1 | t

-- 现在可以重新注册同一邮箱
INSERT INTO user_account (email) VALUES ('alice@example.com');
INSERT 0 1

SELECT id, email, deleted_at IS NOT NULL AS deleted FROM user_account ORDER BY id;
 id |       email       | deleted
----+-------------------+---------
  1 | alice@example.com#1 | t
  2 | alice@example.com   | f
(2 rows)

SELECT user_id, action FROM user_account_log ORDER BY id;
 user_id |    action
---------+---------------
       1 | created
       1 | soft_deleted
       2 | created
(3 rows)

这个模式的关键点：BEFORE 触发器只在「由未删除变为已删除」这个瞬间改写 email，用 OLD.deleted_at IS NULL AND NEW.deleted_at IS NOT NULL 精确界定状态跃迁，避免每次更新都重复加后缀。

注意事项 ​

  • BEFORE 行级忘记 RETURN NEW：函数末尾无 RETURN 时返回 NULL，所有行被静默跳过。-- 错误！
    sql
    CREATE FUNCTION f() RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN
  NEW.updated_at := now();
END;      -- 缺 RETURN NEW; → UPDATE 0
$$;
  • 在 AFTER 行级里改 NEW：改动不生效，却会给后来者错误暗示。-- 错误！
    sql
    NEW.status := 'ok';   -- AFTER 中无效
  • 给 OLD 赋值：OLD 永远只读。-- 错误！
    sql
    OLD.price := 0;   -- ERROR: record "old" is not assignable
  • INSERT 触发器里读 OLD、DELETE 触发器里读 NEW：这两个变量未赋值，运行时报错。-- 错误！
  • 在 BEFORE 里读存储生成列：值尚未计算；赋值会被覆盖。-- 错误！
    sql
    RAISE NOTICE '%', NEW.amount;   -- 生成列，BEFORE 中值未定义
  • 从 AFTER 触发器返回 NEW 并期望它生效：返回值被完全忽略。-- 不推荐
  • 在 BEFORE 里依赖「本语句已处理的行数」：行的处理顺序不可预期，结果不稳定。-- 错误！
    sql
    SELECT count(*) INTO v FROM t;    -- 行序不定，结果不定
  • 把触发器函数声明为 STABLE/IMMUTABLE：会看不到调用语句的修改。-- 错误！
    sql
    CREATE FUNCTION f() RETURNS trigger STABLE LANGUAGE plpgsql AS $$ ... $$;
  • 在行级触发器里做重活：调用次数等于行数，会被成倍放大。-- 不推荐
    sql
    PERFORM pg_sleep(0.001);     -- N 行就是 N 次睡眠
  • 动态拼接 TG_ARGV 里的列名：直接字符串拼接有注入与语法风险，必须用 format(... %I)。-- 错误！
    sql
    EXECUTE 'SELECT ($1).' || TG_ARGV[0] INTO v USING NEW;   -- 危险

最佳实践 ​

  • 行级函数结尾统一写 IF TG_OP = 'DELETE' THEN RETURN OLD; END IF; RETURN NEW;，作为强制模板。
  • 校验写 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)
  • UPDATE OF 与 WHEN 叠加使用：前者减少排队范围，后者排除值未变的伪更新。
  • NEW 的字段赋值集中放在函数开头：便于审查「哪些列会被触发器改写」，避免分散在多个分支里。
  • 抛错时携带 COLUMN / CONSTRAINT 等诊断信息：让 PHP 侧能定位字段。
    sql
    RAISE EXCEPTION '数量必须为正: %', NEW.qty
  USING ERRCODE = '23514', COLUMN = 'qty', TABLE = TG_TABLE_NAME;
  • 行级审计在大数据量下改为语句级 + 过渡表：把 N 次 INSERT 合并为 1 次批量 INSERT。
    sql
    CREATE TRIGGER trg_a AFTER UPDATE ON t
  REFERENCING OLD TABLE AS o NEW TABLE AS n
  FOR EACH STATEMENT EXECUTE FUNCTION bulk_audit();
  • 禁止在行级触发器里发起外部调用：网络往返会被放大 N 倍，且会长时间持锁。
  • 用测试覆盖状态跃迁分支：OLD/NEW 的差异判断是行级触发器最容易写错的地方，逐分支验证。
    sql
    BEGIN;
UPDATE user_account SET deleted_at = now() WHERE id = 1;
SELECT * FROM user_account_log WHERE user_id = 1;
ROLLBACK;
  • 为触发器用途写 COMMENT ON TRIGGER：注明层级、事件、以及它改写了哪些列。
    sql
    COMMENT ON TRIGGER trg_user_account_normalize ON user_account
  IS 'BEFORE ROW：规范 email；软删除时改写 email 以释放唯一性';
  • 大批量导入前评估禁用行级触发器，并在导入后做一致性校验。
  • 能用约束表达的不要用触发器：CHECK、唯一索引、外键、生成列都比行级触发器廉价且声明式。

下一节

继续学习：语句级触发器 FOR EACH STATEMENT

参考链接 ​