PostgreSQL / 系统课时

触发器设计与风险

触发器设计与风险

触发器适合把必须与数据写入保持一致的规则放到数据库端,但也容易把一次简单 DML 变成隐藏的多步事务。本课用行级与语句级触发器对比,说明如何控制递归、副作用和批量写入成本。

学习目标

  • 能根据执行时机与粒度选择 BEFORE、AFTER、行级或语句级触发器
  • 能识别递归写入、外部副作用和批量更新中的放大成本
  • 能用过渡表把批量审计从逐行操作改成集合操作

先按职责选择触发器类型

BEFORE 行级触发器适合校验或改写即将写入的单行,因为它可以直接返回修改后的 NEW,也可以通过返回 NULL 跳过该行。AFTER 触发器看到的是已经完成约束检查和实际写入后的结果,更适合审计、派生记录或通知数据库内的后续逻辑。

行级触发器会对每个受影响行执行一次;语句级触发器无论影响 1 行还是 10 万行,通常只执行一次。因此规则依赖“每一行的新旧值”时选行级,规则只需要处理整批变化时优先语句级。INSTEAD OF 主要用于可更新视图,用来替代原本无法直接执行的写操作。

需求 更合适的触发器
阻止非法余额、规范单行字段 BEFORE ... FOR EACH ROW
记录整批更新的审计明细 AFTER ... FOR EACH STATEMENT + 过渡表
让视图接收插入或更新 INSTEAD OF ... FOR EACH ROW

把执行链和副作用边界画清楚

一次 UPDATE 可能先经过行级校验,再写表,随后触发审计或其他派生写入。只要后续逻辑再次更新原表,就可能进入新的触发器调用,因此设计时必须把“会写哪些表、是否可能回到原表”明确画出来。

PostgreSQL 触发器执行链与递归风险

用过渡表降低批量审计成本

下面的测试在 accounts 上放一个行级 BEFORE UPDATE 触发器负责余额校验,再用语句级 AFTER UPDATE 触发器一次性读取 OLD TABLE 与 NEW TABLE,把本次批量更新的变化集合写入审计表。请在测试数据库中执行。

sql
DROP TABLE IF EXISTS account_audit, accounts CASCADE;

CREATE TABLE accounts (
    id bigint PRIMARY KEY,
    balance numeric(12, 2) NOT NULL
);

CREATE TABLE account_audit (
    account_id bigint NOT NULL,
    old_balance numeric(12, 2) NOT NULL,
    new_balance numeric(12, 2) NOT NULL
);

CREATE OR REPLACE FUNCTION prevent_negative_balance()
RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
    IF NEW.balance < 0 THEN
        RAISE EXCEPTION 'negative balance is not allowed';
    END IF;
    RETURN NEW;
END;
$$;

CREATE OR REPLACE FUNCTION audit_account_batch()
RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
    INSERT INTO account_audit (account_id, old_balance, new_balance)
    SELECT n.id, o.balance, n.balance
    FROM old_rows AS o
    JOIN new_rows AS n USING (id)
    WHERE o.balance IS DISTINCT FROM n.balance;

    RETURN NULL;
END;
$$;

CREATE TRIGGER accounts_balance_guard
BEFORE UPDATE ON accounts
FOR EACH ROW
EXECUTE FUNCTION prevent_negative_balance();

CREATE TRIGGER accounts_batch_audit
AFTER UPDATE ON accounts
REFERENCING OLD TABLE AS old_rows NEW TABLE AS new_rows
FOR EACH STATEMENT
EXECUTE FUNCTION audit_account_batch();

INSERT INTO accounts (id, balance)
VALUES (1, 100.00), (2, 200.00);

UPDATE accounts
SET balance = balance + 10.00;

SELECT account_id, old_balance, new_balance
FROM account_audit
ORDER BY account_id;

预期结果

text
UPDATE 2
 account_id | old_balance | new_balance
------------+-------------+-------------
          1 |      100.00 |      110.00
          2 |      200.00 |      210.00
(2 rows)

不同客户端会额外显示 CREATE TABLE、CREATE FUNCTION、CREATE TRIGGER 等状态行,但最终更新行数和查询结果应与上面一致。

控制递归和放大成本

最危险的递归来自“触发器更新了触发它的同一张表”。如果业务确实需要回写,应优先改成在 BEFORE 触发器里直接修改 NEW,而不是再次执行 UPDATE。pg_trigger_depth() 可以作为保险丝判断当前触发深度,但不应代替清晰的数据流设计,否则触发器之间会形成隐含的执行顺序依赖。

批量 DML 还要关注成本放大。行级触发器会执行与受影响行数近似成正比的函数调用,如果函数里再发起单行 SQL,就可能把一次集合更新退化成大量随机访问。能用过渡表、一次 INSERT ... SELECT 或一次聚合完成的工作,应避免逐行循环。

上线前检查边界

上线前至少确认三件事:触发器是否会写回原表或形成跨表环路;失败时所有数据库内副作用是否能随事务一起回滚;对 1 行和大批量写入的耗时是否都可接受。触发器最适合承载短小、确定、事务内的规则;一旦逻辑需要远程调用、复杂编排或长时间处理,就应把它移出触发器路径。

按自己的节奏,确认真正理解。

目录与阅读设置

阅读设置

17

记录一个想法

触发器设计与风险

0 / 10000 · 笔记保存到账号。未提交的修改会在关闭、离开或刷新后丢失。

登录哪都通