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

用过渡表降低批量审计成本
下面的测试在 accounts 上放一个行级 BEFORE UPDATE 触发器负责余额校验,再用语句级 AFTER UPDATE 触发器一次性读取 OLD TABLE 与 NEW TABLE,把本次批量更新的变化集合写入审计表。请在测试数据库中执行。
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;预期结果
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 行和大批量写入的耗时是否都可接受。触发器最适合承载短小、确定、事务内的规则;一旦逻辑需要远程调用、复杂编排或长时间处理,就应把它移出触发器路径。