PHP小码农

Appearance

规则与触发器对比 ​

规则（Rule）与触发器（Trigger）都能实现「某条 SQL 发生时顺带做点别的事」，因此经常被拿来比较。但两者处于查询处理流水线的不同阶段：规则工作在重写期（Rewrite Phase），拿到的是查询树而不是数据；触发器工作在执行期（Execution Phase），拿到的是真实的行。

这个阶段差异决定了一切：规则无法报错、无法做数据相关分支、无法实现外键，但改写成本与影响行数无关；触发器能报错、能查任意表、能逐行决策，但每行都要回调一次。理解这条分界线，就能在「可更新视图」「审计日志」「级联清理」等场景中一次选对工具。

前置知识

阅读本节前，建议先了解：CREATE RULE 规则、INSTEAD 规则、可更新视图

基础概念 ​

两条不同的执行路径 ​

text
SQL 文本 → Parser（语法分析） → Rewriter（重写期，规则在此生效）
        → Planner（生成计划） → Executor（执行期，触发器在此生效）

规则（Rule）      ：改写查询树 → 生成额外查询树 → 原查询可被丢弃
触发器（Trigger） ：逐行 / 按语句回调函数 → 查询数据、抛错、改行

一句话概括：规则改写语句，触发器处理行。

工具何时介入看到什么能做什么
规则重写期查询树（还没执行）改写语句、生成额外语句、丢弃语句
触发器执行期真实的行与事务状态改行、查表、报错、条件分支、延迟到事务末

为什么「阶段」如此重要 ​

规则在重写期运行时，数据库里一行数据都还没有被处理。因此规则不可能根据「另一张表里有没有对应记录」来决定是执行还是报错——它只能看查询树里的 NEW / OLD 伪关系。这正是规则实现不了外键（Foreign Key）语义的根本原因。

触发器在执行期被调用，此时当前行已在内存中、当前事务里此前语句的修改也已可见，因此它可以：查询任意表并据结果决定行为、RAISE EXCEPTION 让语句回滚、在 BEFORE 阶段改写 NEW、调用带循环与异常处理的过程逻辑、延迟到事务末执行（约束触发器）、在 TRUNCATE 上触发、与 ON CONFLICT 共存。这些都是规则做不到的。

规则与触发器的能力对照表 ​

能力规则（Rule）触发器（Trigger）
触发事件SELECT/INSERT/UPDATE/DELETEINSERT/UPDATE/DELETE/TRUNCATE
能否报告错误❌ 只能静默 NOTHING✅ RAISE EXCEPTION
能否查询其它表只能在动作里写子查询（执行期）✅ 每个回调内随时查询
能否按行决策❌ 一次改写✅ FOR EACH ROW
能否实现外键❌✅（或直接用 FOREIGN KEY）
影响行数相关性改写成本与行数无关每行回调一次，成本正比行数
参数化 / 计划复用无「每行」概念SPI 计划可缓存复用
能否延迟执行❌✅ 约束触发器 SET CONSTRAINTS
RETURNING 支持有限（单动作无条件 INSTEAD）✅ 通过返回值提供行
与 ON CONFLICT 共存❌ 官方明确不支持✅
定义位置仅关系（表/视图）表/视图/外部表
元数据目录pg_rewrite / pg_rulespg_trigger

语法与用法 ​

一条「级联清理」需求，两种写法。

规则写法：

text
CREATE [ OR REPLACE ] RULE name AS ON event
    TO table_name [ WHERE condition ]
    DO [ ALSO | INSTEAD ] { NOTHING | command | ( command ; command ... ) }

-- event：SELECT | INSERT | UPDATE | DELETE
sql
CREATE TABLE computer (
  hostname     text PRIMARY KEY,   -- 索引
  manufacturer text
);

CREATE TABLE software (
  software text,
  hostname text REFERENCES computer(hostname) ON DELETE CASCADE
);

-- 用规则实现「删除主机时清理其软件记录」
CREATE RULE computer_del AS ON DELETE TO computer
  DO ALSO DELETE FROM software WHERE hostname = OLD.hostname;
text
INSERT INTO computer VALUES ('pc1', 'bim'), ('pc2', 'bim');
INSERT INTO software VALUES ('editor', 'pc1'), ('player', 'pc1');

DELETE FROM computer WHERE hostname = 'pc1';
SELECT * FROM software;
 software | hostname
----------+----------
 player   | pc1        -- 只剩 1 行，另一行被规则动作删掉了

触发器写法：

text
CREATE [ OR REPLACE ] TRIGGER name
    { BEFORE | AFTER | INSTEAD OF } { INSERT | UPDATE | DELETE | TRUNCATE }
    ON table_name
    [ FOR [ EACH ] { ROW | STATEMENT } ]
    [ WHEN ( condition ) ]
    EXECUTE { FUNCTION | PROCEDURE } function_name ( arguments )
sql
CREATE FUNCTION trg_computer_del() RETURNS trigger
LANGUAGE plpgsql AS $$
BEGIN
  DELETE FROM software WHERE hostname = OLD.hostname;   -- 可看到当前行
  RETURN OLD;
END;
$$;

CREATE TRIGGER computer_del_trg
  AFTER DELETE ON computer
  FOR EACH ROW EXECUTE FUNCTION trg_computer_del();
text
DELETE FROM computer WHERE hostname = 'pc2';

SELECT * FROM software WHERE hostname = 'pc2';
 software | hostname
----------+----------
(0 rows)        -- 同样清理干净

两份写法结果一致，但代价模型完全不同：规则生成一条额外 DELETE，触发器执行每行一次 DELETE。

详细说明 ​

规则与触发器的本质差异：重写期 vs 执行期 ​

规则是查询树变换。它的输入是语法树，输出是一个「查询树列表」，系统拿到这个列表后逐个优化、执行。

sql
CREATE TABLE shoelace_data (sl_name text PRIMARY KEY, sl_avail int, sl_color text);
CREATE TABLE shoelace_log (sl_name text, sl_avail int, log_who text, log_when timestamptz);

CREATE RULE log_shoelace AS ON UPDATE TO shoelace_data
  WHERE NEW.sl_avail <> OLD.sl_avail
  DO ALSO INSERT INTO shoelace_log
     VALUES (NEW.sl_name, NEW.sl_avail, current_user, current_timestamp);
sql
UPDATE shoelace_data SET sl_avail = 6 WHERE sl_name = 'sl7';
text
-- 规则系统实际产出两条查询树（示意）：
[1] INSERT INTO shoelace_log VALUES (shoelace_data.sl_name, 6, current_user, now())
      FROM shoelace_data
     WHERE 6 <> shoelace_data.sl_avail
       AND shoelace_data.sl_name = 'sl7';
[2] UPDATE shoelace_data SET sl_avail = 6 WHERE sl_name = 'sl7';

触发器是执行期回调。它不改写语句，而是在每条语句、每一行上被调用一次，函数体里是真正的过程代码。

sql
CREATE FUNCTION trg_log_shoelace() RETURNS trigger
LANGUAGE plpgsql AS $$
BEGIN
  IF NEW.sl_avail IS DISTINCT FROM OLD.sl_avail THEN
    INSERT INTO shoelace_log
    VALUES (NEW.sl_name, NEW.sl_avail, current_user, current_timestamp);
  END IF;
  RETURN NEW;
END;
$$;

CREATE TRIGGER log_shoelace_trg
  AFTER UPDATE OF sl_avail ON shoelace_data
  FOR EACH ROW EXECUTE FUNCTION trg_log_shoelace();

关键结论

规则把「做额外事情」表达为多一条 SQL 语句；触发器把它表达为一段过程代码。前者在优化器视野内（可被整体优化），后者在优化器视野外（逐行执行）。

触发器能做什么而规则不能：约束与报错 ​

官方文档明确指出：规则无法实现某些类型的约束，尤其是外键。规则可以把不满足条件的命令重写成 NOTHING，但数据会被静默丢弃，调用方毫不知情——语义上完全不等同于「报错并回滚」。

sql
-- 规则版「伪外键」：值不存在时静默丢弃 —— 危险
CREATE RULE order_buyer_check AS ON INSERT TO orders
  WHERE NOT EXISTS (SELECT 1 FROM buyers WHERE id = NEW.buyer_id)
  DO INSTEAD NOTHING;
text
INSERT INTO orders (buyer_id, amount) VALUES (99999, 100.00);
INSERT 0 0        -- 什么都没插入，也没有任何错误！

触发器可以做真正的校验：

sql
CREATE FUNCTION trg_check_buyer() RETURNS trigger
LANGUAGE plpgsql AS $$
BEGIN
  IF NOT EXISTS (SELECT 1 FROM buyers WHERE id = NEW.buyer_id) THEN
    RAISE EXCEPTION 'buyer % does not exist', NEW.buyer_id
      USING ERRCODE = 'foreign_key_violation';
  END IF;
  RETURN NEW;
END;
$$;

CREATE TRIGGER check_buyer_trg
  BEFORE INSERT OR UPDATE OF buyer_id ON orders
  FOR EACH ROW EXECUTE FUNCTION trg_check_buyer();
text
INSERT INTO orders (buyer_id, amount) VALUES (99999, 100.00);
ERROR:  buyer 99999 does not exist
CONTEXT:  PL/pgSQL function trg_check_buyer() line 4 at RAISE

能声明约束就别自己写

需要外键就用 FOREIGN KEY 声明，而不是手写触发器或规则。手写方案会丢失 NOT VALID、SET CONSTRAINTS、pg_dump 还原顺序、分区表克隆等一整套基础设施。

规则不能实现外键的原因 ​

根本原因仍是阶段差异：规则只看得到查询树，看不到数据。外键检查的语义是「插入的 buyer_id 必须在 buyers 中存在」，而「是否存在」只有执行期用索引探测才能知道。

text
规则可引用的信息：NEW.<列>、OLD.<列>、由两者构造的表达式
                  ✗ 无法表达「另一张表里是否存在该值」

触发器可引用：NEW、OLD，以及任意时刻的 SELECT 结果

规则动作里当然可以写子查询，但那已经是执行期的语句了——规则本身无法据其分支去「决定要不要报错」。

sql
-- 子查询在执行期求值，但规则无法据此改变行为
CREATE RULE x AS ON DELETE TO computer
  DO ALSO DELETE FROM software
            WHERE hostname = OLD.hostname
              AND EXISTS (SELECT 1 FROM audit WHERE obj = OLD.hostname);

逐行执行成本 vs 一次改写 ​

这是选型中最常被问到的一点，两者的成本结构完全不同：

text
影响 N 行的一条语句：

规则：  Rewriter 改写 1 次 → 生成 1 条额外 SQL → 该 SQL 由优化器统一计划并执行 1 次
        成本 ≈ O(1) 次额外语句执行（与 N 无关）

触发器（FOR EACH ROW）：
        执行器逐行回调 N 次 → 每次调用都经 SPI 管理器进入函数
        成本 ≈ O(N) 次函数调用 + N 次内部语句执行

什么时候规则更快

一条语句影响大量行、且规则的额外动作能生成可索引的等值连接时，规则通常明显更快：规则只需执行一条 SQL，优化器会为它统一选择嵌套循环或哈希连接；触发器则要把同样的逻辑跑成千上万次。

什么时候触发器更快

每行逻辑复杂（查多张表、条件分支、报错），或规则的额外动作导致大范围且限定不佳的连接（优化器选错计划）时，触发器反而更快也更可控。官方总结：规则只有在「动作产生大规模且限定不佳的连接」这一优化器失效的情形下，才会显著慢于触发器。

官方 computer / software 性能例子 ​

官方文档用 computer / software 两张表演示了这个对比。两表都有数千行，hostname 上都有唯一索引，需求是「删除主机时连带删除其软件记录」。

sql
CREATE TABLE computer (
  hostname     text PRIMARY KEY,
  manufacturer text
);
CREATE INDEX comp_manufidx ON computer (manufacturer);

CREATE TABLE software (
  software text,
  hostname text
);
CREATE INDEX soft_hostidx ON software (hostname);

-- 规则版
CREATE RULE computer_del AS ON DELETE TO computer
  DO ALSO DELETE FROM software WHERE hostname = OLD.hostname;

情形一：只删一行（DELETE ... WHERE hostname = 'mypc.local.net'）。规则生成的额外语句退化为「两个索引扫描的嵌套循环」，与触发器差别不大：

text
Nested Loop
  ->  Index Scan using computer_pkey on computer
  ->  Index Scan using soft_hostidx on software

情形二：删除 2000 台 hostname 以 old 开头的主机。 条件的写法直接决定规则版的计划：

sql
-- 写法 A：两个不等式，规划器无法把范围条件下推到 software
DELETE FROM computer WHERE hostname >= 'old' AND hostname < 'ole';
-- → Hash Join（对 software 做 Seq Scan）

-- 写法 B：正则形式反而能下推，两次索引扫描
DELETE FROM computer WHERE hostname ~ '^old';
-- → Nested Loop（两次 Index Scan）

情形三：按厂商删除（WHERE manufacturer = 'bim'）走 comp_manufidx，同样是两次索引扫描的 Nested Loop。

情形规则触发器
单行删除1 次改写 + 1 条语句1 次 SPI 调用
2000 行删除（范围条件）1 条语句，但可能退化为对 software 的 Seq Scan2000 次索引扫描 + 2000 次 SPI 调用
2000 行删除（正则可下推）1 条语句，两次索引扫描同上，逐行开销

官方结论：规则的额外命令成本基本与影响行数无关；规则只在额外动作产生「大规模且限定不佳的连接」（优化器失效）时才会显著慢于触发器。用 EXPLAIN (ANALYZE, BUFFERS) 分别跑两种实现即可验证：规则版只出现一条额外语句的计划，触发器版的 Execution Time 会随删除行数线性增长。

数据可见性与 volatile 函数 ​

规则在重写期运行，此时毫无数据可言；而规则生成的额外语句在执行期运行时，又受语句执行顺序影响。官方两处要点：

text
规则与原始语句的执行顺序：
  ON INSERT 规则：原语句先执行            → 规则动作能看到刚插入的行
  ON UPDATE / ON DELETE 规则：规则动作先执行 → 动作能看到待改/待删的行

触发器顺序：
  BEFORE 触发器：行改变之前，可改 NEW
  AFTER  触发器：行改变之后，所有修改（含其它触发器效果）可见
sql
-- ON DELETE 规则需先执行，才能看到待删的行
CREATE RULE order_del AS ON DELETE TO orders
  DO ALSO INSERT INTO orders_archive
     SELECT OLD.order_id, OLD.buyer_id, OLD.amount, OLD.status, now();

volatile 函数是规则最容易被忽视的坑。官方明确警告：当原始查询包含 volatile 函数时，规则的行为会出人意料——函数可能被执行比预期更多的次数，因为规则动作与原始查询会各自对同一表达式求值一次。

sql
-- 规则版：random() 可能被算两次
CREATE RULE r AS ON INSERT TO t
  DO ALSO INSERT INTO t_log (id, rnd) VALUES (NEW.id, random());

INSERT INTO t (id, val) VALUES (1, random());   -- 改写后 random() 可能多次求值

volatile 函数是规则的雷区

nextval()、random()、clock_timestamp()、带副作用的函数在规则下都可能被多次求值。对这类逻辑优先用触发器（函数体内调用，次数与行数一一对应）；规则适合纯关系代数式的改写。

另外，规则类型还不支持原查询中的 WITH 子句，以及 UPDATE ... SET 列表中的多赋值子查询（SET x = (SELECT ...)），因为这些构造被复制进规则查询后会导致子查询被多次求值。

sql
-- 有 INSERT/UPDATE 规则时，这条会报错
INSERT INTO v_orders (order_id, amount) VALUES (1, 10.00)
  ON CONFLICT (order_id) DO NOTHING;
text
ERROR:  INSERT with ON CONFLICT clause cannot be used with table that has INSERT or UPDATE rules

INSTEAD OF 触发器 vs INSTEAD 规则：写法对比 ​

两者都能让复杂视图「可写」。先用 INSTEAD 规则：

sql
CREATE VIEW v_orders AS
SELECT o.order_id, o.buyer_id, b.name AS buyer_name, o.amount, o.status
  FROM orders o JOIN buyers b ON b.id = o.buyer_id;

CREATE RULE v_orders_ins AS ON INSERT TO v_orders
  DO INSTEAD INSERT INTO orders (order_id, buyer_id, amount, status)
              VALUES (NEW.order_id, NEW.buyer_id, NEW.amount, NEW.status);

再用 INSTEAD OF 触发器：

sql
CREATE FUNCTION v_orders_insert() RETURNS trigger
LANGUAGE plpgsql AS $$
BEGIN
  INSERT INTO orders (order_id, buyer_id, amount, status)
  VALUES (NEW.order_id, NEW.buyer_id, NEW.amount, NEW.status);
  RETURN NEW;                  -- 返回 NEW，供 RETURNING 使用
END;
$$;

CREATE TRIGGER v_orders_ins_trg
  INSTEAD OF INSERT ON v_orders
  FOR EACH ROW EXECUTE FUNCTION v_orders_insert();

差异一览：

维度INSTEAD 规则INSTEAD OF 触发器
定义限制无特殊限制必须 FOR EACH ROW，且只能建在视图上
WHEN 条件支持规则 WHERE 条件（基于 NEW/OLD）不支持 WHEN 条件
报错 / 校验只能静默 NOTHING✅ RAISE EXCEPTION
过程逻辑无（只有 SQL 改写）✅ 循环、分支、异常处理
RETURNING需单动作 + 无条件 INSTEAD，且 OLD/NEW 语义有坑直接 RETURN NEW（或 OLD）即可
多表分发一条规则可含多动作（但失去 RETURNING）函数体内任意多条语句
性能（单表映射、大批量）通常更优（一条额外语句）逐行回调
可读性较隐晦直观、官方推荐

官方倾向

官方原文指出：本章所有更新规则示例都可以用视图上的 INSTEAD OF 触发器实现，而且编写这种触发器通常比编写规则更容易，尤其在更新逻辑复杂时。所以：简单映射用规则求性能，复杂逻辑用触发器求正确与可维护。

一个必须记住的联动规则：

text
视图上的写操作由谁处理，决定谁会触发：

  ① 由行级 INSTEAD OF 触发器处理
        → 视图上的语句级触发器会被触发
  ② 由 INSTEAD 规则处理
        → 视图上的触发器不会被触发；
          触发的是规则生成的替换语句所涉及的表上的触发器
  ③ 视图本身可自动更新
        → 触发的是基表的语句级触发器

可更新视图两种实现方式的优缺点 ​

sql
-- 先检查视图是否本身就能自动更新
SELECT table_name, is_updatable, is_insertable_into
  FROM information_schema.views
 WHERE table_name = 'v_orders';
text
 table_name | is_updatable | is_insertable_into
------------+--------------+--------------------
 v_orders   | NO           | NO
(1 row)
方案优点缺点
自动可更新视图零维护、优化器可整体优化、无额外对象仅限单表简单视图，列没有派生/聚合
INSTEAD 规则改写成本与影响行数无关；一条额外语句即可；多动作可分发无法报错；RETURNING 限制多；OLD/NEW 语义隐晦；volatile 函数有坑
INSTEAD OF 触发器逻辑直观；可校验报错；RETURNING 简单；能调任意其它表逐行回调，行多时慢；需维护函数与触发器对象

选型动作序列：

text
① 视图能否自动更新？  → 是：什么都不做（最优）
② 写操作是「单表字段一一映射」且批量大？  → 规则（DO INSTEAD）
③ 需要校验、报错、分支、跨表副作用？     → INSTEAD OF 触发器
④ 只需「只读保护」？                     → 规则 NOTHING（静默）
                                           或 INSTEAD OF 触发器 RAISE（要报错时）

典型场景选型：日志、审计、跨表写入 ​

场景一：审计日志（记录谁改了什么）。

sql
-- 规则版：动作先于 UPDATE，条件基于 NEW/OLD
CREATE RULE orders_audit AS ON UPDATE TO orders
  WHERE NEW.status IS DISTINCT FROM OLD.status
  DO ALSO INSERT INTO orders_status_audit
     VALUES (OLD.order_id, OLD.status, NEW.status, current_user, clock_timestamp());
sql
-- 触发器版：可精确到列，且用 WHEN 提前过滤
CREATE FUNCTION trg_status_audit() RETURNS trigger
LANGUAGE plpgsql AS $$
BEGIN
  INSERT INTO orders_status_audit
  VALUES (OLD.order_id, OLD.status, NEW.status, current_user, clock_timestamp());
  RETURN NULL;                       -- AFTER 触发器返回值被忽略
END;
$$;

CREATE TRIGGER orders_audit_trg
  AFTER UPDATE OF status ON orders
  FOR EACH ROW
  WHEN (OLD.status IS DISTINCT FROM NEW.status)   -- 提前过滤，减少回调
  EXECUTE FUNCTION trg_status_audit();

何时选触发器写审计

需要记录操作者会话信息、需要在日志里再查一次表、需要区分 INSERT 与 ON CONFLICT DO UPDATE、或需要延迟到事务末写入时，用触发器。纯「复制几个旧值到日志表」且批量很大时，规则更省。

场景二：跨表分发写入。

sql
-- 规则：一条语句写两张表（多动作），但失去 RETURNING
CREATE RULE v_push_ins AS ON INSERT TO v_push
  DO INSTEAD (
    INSERT INTO t_cn (id, v) VALUES (NEW.id, NEW.v);
    INSERT INTO t_other (id, v) VALUES (NEW.id, NEW.v);
  );
sql
-- 触发器：可在函数内按数据决定写哪张表
CREATE FUNCTION v_push_insert() RETURNS trigger
LANGUAGE plpgsql AS $$
BEGIN
  IF NEW.region = 'cn' THEN
    INSERT INTO t_cn (id, v) VALUES (NEW.id, NEW.v);
  ELSE
    INSERT INTO t_other (id, v) VALUES (NEW.id, NEW.v);
  END IF;
  RETURN NEW;
END;
$$;

别用规则或触发器实现外键

任何「级联清理」如果用规则或触发器手写，都无法复现外键的完整语义（并发下的锁、NOT VALID、SET CONSTRAINTS、分区克隆、pg_dump 还原顺序）。真正的需求是外键时，直接声明 FOREIGN KEY ... ON DELETE CASCADE。

场景三：只需「只读保护」。 静默屏蔽用规则，需要报错用触发器：

sql
-- 静默丢弃（规则）
CREATE RULE v_x_del AS ON DELETE TO v_x DO INSTEAD NOTHING;

-- 明确报错（INSTEAD OF 触发器，更安全）
CREATE OR REPLACE FUNCTION v_x_no_delete() RETURNS trigger
LANGUAGE plpgsql AS $$
BEGIN
  RAISE EXCEPTION 'view % is read-only', TG_TABLE_NAME;
END;
$$;
CREATE TRIGGER v_x_del_trg
  INSTEAD OF DELETE ON v_x
  FOR EACH ROW EXECUTE FUNCTION v_x_no_delete();

选型决策树与混合使用 ​

text
问：这件事能不能用声明式约束（CHECK / UNIQUE / FK / EXCLUDE）解决？
 ├─ 能  → 用约束（最好；不写规则也不写触发器）
 └─ 不能 ↓
      问：需要「报错 / 分支 / 查其它表 / 过程逻辑」吗？
       ├─ 需要 → 用触发器
       │   问：需要延迟到事务末吗？
       │    ├─ 需要 → CONSTRAINT TRIGGER（AFTER ROW + DEFERRABLE）
       │    └─ 不需要 → BEFORE/AFTER（行级或语句级）
       └─ 不需要 ↓
            问：是不是「一条语句映射成另一条等值条件语句」？
             ├─ 是（大批量、字段一一对应）→ 用规则（DO ALSO / DO INSTEAD）
             └─ 否 ↓
                  问：只需静默屏蔽某操作？
                   ├─ 是 → DO INSTEAD NOTHING（规则，静默）
                   └─ 否（要报错）→ 回到触发器

混合使用的典型模式：

sql
-- ① 规则负责「写归档」（纯搬值、批量友好）
CREATE RULE orders_log AS ON DELETE TO orders
  DO ALSO INSERT INTO orders_archive
     SELECT OLD.*, current_user, clock_timestamp();

-- ② 触发器负责「校验并报错」（需要过程逻辑）
CREATE FUNCTION orders_check_amount() RETURNS trigger
LANGUAGE plpgsql AS $$
BEGIN
  IF NEW.amount <= 0 THEN
    RAISE EXCEPTION 'amount must be positive, got %', NEW.amount;
  END IF;
  RETURN NEW;
END;
$$;

CREATE TRIGGER orders_check_amount_trg
  BEFORE INSERT OR UPDATE OF amount ON orders
  FOR EACH ROW EXECUTE FUNCTION orders_check_amount();

同一事件上规则与触发器的执行关系

在表上：规则产生的语句被当作独立语句执行，因此会正常触发目标表上的触发器——级联效果是「规则定结构、触发器定细节」。 在视图上：若写操作由 INSTEAD 规则接管，视图自身的语句级触发器不会被触发（见前一节对照表）。

实战示例 ​

用一套「订单 + 订单明细」模型，把校验、审计、级联三件事分别交给合适的工具。

第一步：建表。

sql
CREATE TABLE buyers (
  id   integer PRIMARY KEY,
  name text NOT NULL
);

CREATE TABLE orders (
  order_id  integer PRIMARY KEY,
  buyer_id  integer NOT NULL REFERENCES buyers(id),
  amount    numeric(12,2) NOT NULL,
  status    text NOT NULL DEFAULT 'new',
  updated_at timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE orders_status_audit (
  order_id   integer, old_status text, new_status text,
  changed_by text,    changed_at timestamptz
);

INSERT INTO buyers VALUES (1, 'Alice'), (2, 'Bob');

第二步：用触发器做校验与自动填值（需要过程逻辑）。

sql
CREATE FUNCTION orders_before_write() RETURNS trigger
LANGUAGE plpgsql AS $$
BEGIN
  IF NEW.amount <= 0 THEN
    RAISE EXCEPTION 'amount must be positive, got %', NEW.amount;
  END IF;
  NEW.updated_at := clock_timestamp();
  RETURN NEW;                      -- BEFORE 触发器可改写 NEW
END;
$$;

CREATE TRIGGER orders_before_write_trg
  BEFORE INSERT OR UPDATE ON orders
  FOR EACH ROW EXECUTE FUNCTION orders_before_write();
sql
INSERT INTO orders (order_id, buyer_id, amount, status) VALUES (1, 1, 199.00, 'new');   -- INSERT 0 1
INSERT INTO orders (order_id, buyer_id, amount, status) VALUES (2, 1, -5.00, 'new');
-- ERROR:  amount must be positive, got -5.00

第三步：用规则做状态审计（纯搬值、批量友好）。

sql
CREATE RULE orders_status_log AS ON UPDATE TO orders
  WHERE NEW.status IS DISTINCT FROM OLD.status
  DO ALSO INSERT INTO orders_status_audit
     VALUES (OLD.order_id, OLD.status, NEW.status, current_user, clock_timestamp());
text
UPDATE orders SET status = 'paid' WHERE order_id = 1;       -- UPDATE 1
SELECT order_id, old_status, new_status, changed_by FROM orders_status_audit;
 order_id | old_status | new_status | changed_by
----------+------------+------------+------------
        1 | new        | paid       | postgres

第四步：验证规则「动作先于 UPDATE」的可见性——只改金额不改状态，不应产生审计行。

sql
UPDATE orders SET amount = 259.00 WHERE order_id = 1;
SELECT count(*) FROM orders_status_audit;
-- UPDATE 1，count 仍为 1：规则条件 NEW.status <> OLD.status 不成立

第五步：级联清理——规则版与触发器版的成本差异。

sql
CREATE TABLE order_items (
  order_id integer NOT NULL REFERENCES orders(order_id),
  sku      text NOT NULL,
  qty      integer NOT NULL
);
CREATE INDEX order_items_order_id_idx ON order_items (order_id);

INSERT INTO order_items
SELECT 1, 'sku-' || g, 1 FROM generate_series(1, 500) AS g;

-- 规则版：一条额外语句 DELETE ... WHERE order_id = OLD.order_id
CREATE RULE orders_del_items AS ON DELETE TO orders
  DO ALSO DELETE FROM order_items WHERE order_id = OLD.order_id;
text
规则版：一次计划、一次执行下面这条额外语句
触发器版：AFTER DELETE ON orders FOR EACH ROW 的函数体内执行同样的 DELETE，
          但它是「1 次 DELETE 订单」+「N 次上述语句」，N = 被删订单数

EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF)
DELETE FROM order_items WHERE order_id = 1;

 Delete on order_items (actual rows=500 loops=1)
   ->  Index Scan using order_items_order_id_idx on order_items (actual rows=500 loops=1)

第六步：核对元数据。

sql
SELECT tablename, rulename
  FROM pg_rules WHERE tablename IN ('orders', 'order_items')
 ORDER BY tablename, rulename;

SELECT tgname, tgrelid::regclass AS tbl, tgtype, tgisinternal
  FROM pg_trigger WHERE NOT tgisinternal
 ORDER BY tbl, tgname;
text
 tablename |      rulename
-----------+--------------------
 orders    | orders_status_log
(1 row)

           tgname           |    tbl     | tgtype | tgisinternal
----------------------------+------------+--------+--------------
 orders_before_write_trg    | orders     |      7 | f
(1 row)

实战小结

  • 校验与自动填值 → 触发器（BEFORE，可改 NEW、可报错）；
  • 纯值搬运的审计 → 规则（DO ALSO，批量友好）；
  • 级联清理 → 优先声明式外键；手写时大批量用规则、需延迟/复杂条件用约束触发器；
  • 视图可写化 → 简单映射用 INSTEAD 规则，复杂逻辑用 INSTEAD OF 触发器。

注意事项 ​

  • 错误！用规则实现外键或唯一性校验。 规则只能把命令重写成 NOTHING，数据被静默丢弃，调用方拿到的是「成功但零行」的假象。

    sql
    -- 危险：非法值被静默吞掉
CREATE RULE r AS ON INSERT TO t
  WHERE NOT EXISTS (SELECT 1 FROM ref WHERE id = NEW.ref_id)
  DO INSTEAD NOTHING;

  • 错误！在规则动作里依赖 volatile 函数只求值一次。 官方明确警告 volatile 函数在规则下可能被执行多次。

    sql
    -- random() / nextval() 可能被重复求值
CREATE RULE r AS ON INSERT TO t DO ALSO INSERT INTO log VALUES (random());

  • 错误！指望规则能做数据相关的分支。 规则条件只能引用 NEW/OLD，不能基于其它表的状态分支。

    sql
    -- 规则里无法表达「若 buyers 中不存在则拒绝」

  • 不推荐：给本身可自动更新的视图再建 INSTEAD 规则或 INSTEAD OF 触发器。 自动改写通常计划更优、维护成本为零。

    sql
    SELECT is_updatable, is_insertable_into
  FROM information_schema.views WHERE table_name = 'v_simple';

  • 错误！在有 INSERT/UPDATE 规则的表上使用 ON CONFLICT。

    sql
    INSERT INTO v_orders (order_id, amount) VALUES (1, 10) ON CONFLICT (order_id) DO NOTHING;
    text
    ERROR:  INSERT with ON CONFLICT clause cannot be used with table that has INSERT or UPDATE rules

  • 错误！在有规则的关系上使用带 WITH 子句或 SET x = (SELECT ...) 多赋值的原查询。 这两类构造官方明确不支持。

  • 错误！给构建在视图上的写操作只配条件 INSTEAD 规则。 缺少无条件 INSTEAD 时会报错。

    text
    ERROR:  cannot perform INSERT RETURNING on relation "v_orders"
HINT:  You need an unconditional ON INSERT DO INSTEAD rule with a RETURNING clause.

  • 错误！给 INSTEAD OF 触发器写 WHEN 条件。 官方明确不支持 INSTEAD OF 触发器的 WHEN。

    sql
    -- 非法
CREATE TRIGGER x INSTEAD OF INSERT ON v
  FOR EACH ROW WHEN (NEW.amount > 0) EXECUTE FUNCTION f();

  • 错误！在表上建 INSTEAD OF 触发器，或反过来在视图上建行级 BEFORE/AFTER 触发器。 INSTEAD OF 只能建在视图上且必须 FOR EACH ROW；视图上的 BEFORE/AFTER 只能是语句级。

    text
    视图上：BEFORE/AFTER ... FOR EACH STATEMENT  ✅
        INSTEAD OF ... FOR EACH ROW          ✅
表上  ：BEFORE/AFTER ... FOR EACH ROW|STMT   ✅
        INSTEAD OF                           ❌

  • 错误！以为规则生成的替换语句不会触发触发器。 规则生成的语句是独立语句，会正常触发其目标表上的触发器。

  • 不推荐：只统计「有没有发生写入」却用行级触发器。 用 FOR EACH STATEMENT 即可，避免 N 次回调。

    sql
    CREATE TRIGGER orders_stmt_trg
  AFTER INSERT ON orders
  FOR EACH STATEMENT EXECUTE FUNCTION bump_table_counter();

最佳实践 ​

  • 能用声明式约束就用约束。 CHECK、UNIQUE、FOREIGN KEY、EXCLUDE 优先于任何规则/触发器方案。

    sql
    ALTER TABLE software
  ADD CONSTRAINT software_computer_fk
  FOREIGN KEY (hostname) REFERENCES computer(hostname) ON DELETE CASCADE;

  • 需要「报错」的场景一律用触发器，并用 RAISE ... USING ERRCODE 给出规范错误码。

    sql
    RAISE EXCEPTION 'buyer % does not exist', NEW.buyer_id
  USING ERRCODE = 'foreign_key_violation';

  • 大批量、纯改写、无分支的场景用规则，享受「一条额外语句」的常数成本。

    sql
    CREATE RULE computer_del AS ON DELETE TO computer
  DO ALSO DELETE FROM software WHERE hostname = OLD.hostname;

  • 规则动作务必写成可索引的等值条件，避免大范围且限定不佳的连接导致优化器选错计划。

    sql
    -- 好：等值条件可下推成索引扫描
DO ALSO DELETE FROM software WHERE hostname = OLD.hostname;

  • 触发器用 WHEN 提前过滤，并用 AFTER UPDATE OF <列> 限定触发时机。

    sql
    CREATE TRIGGER orders_audit_trg
  AFTER UPDATE OF status ON orders
  FOR EACH ROW
  WHEN (OLD.status IS DISTINCT FROM NEW.status)
  EXECUTE FUNCTION trg_status_audit();

  • 视图可写化优先 INSTEAD OF 触发器（逻辑直观、可报错、RETURNING 简单）；只有简单映射且批量大时才用 INSTEAD 规则。

    sql
    CREATE TRIGGER v_orders_ins_trg
  INSTEAD OF INSERT ON v_orders
  FOR EACH ROW EXECUTE FUNCTION v_orders_insert();

  • 只读保护若要报错就用触发器，别用静默 DO INSTEAD NOTHING。

    sql
    RAISE EXCEPTION 'view % is read-only', TG_TABLE_NAME;

  • 避免在规则动作中使用 volatile 函数；如确需使用，改到触发器里执行。

    sql
    -- 触发器内调用 random()/clock_timestamp()，调用次数与行数一一对应

  • 对性能敏感的改写用 EXPLAIN (ANALYZE, BUFFERS) 实测两条路径，不要凭直觉断言「触发器一定慢」或「规则一定快」。

    sql
    EXPLAIN (ANALYZE, BUFFERS) DELETE FROM computer WHERE hostname = 'mypc.local.net';

  • 用 pg_rules / pg_trigger 建立上线检查清单，防止规则与触发器相互叠加导致行为不可预期，并在文档中记录选型理由。

    sql
    SELECT tablename, rulename FROM pg_rules WHERE schemaname = 'public';
SELECT tgname, tgrelid::regclass FROM pg_trigger WHERE NOT tgisinternal;

下一节

继续学习：PL/pgSQL 概述

参考链接 ​