PHP小码农

Appearance

过渡表 REFERENCING ​

过渡表（transition relation / transition table）让 AFTER 触发器能够访问「本条语句影响的整批行」，而不只是一行。在 CREATE TRIGGER 里用 REFERENCING OLD TABLE AS old_rows NEW TABLE AS new_rows 给它们命名，触发器函数里就可以像查普通只读临时表一样查询这两张表。它是把「逐行触发器」改造成「每语句一次的函数调用 + 一次批量 SQL」的关键手段，也是大批量数据场景下触发器性能优化的主要武器。

前置知识

阅读本节前，建议先了解：语句级触发器 FOR EACH STATEMENT

基础概念 ​

过渡表是什么 ​

text
UPDATE products SET price = price * 1.1 WHERE category = 'fruit';   -- 影响 1000 行

行级触发器视角：函数被调用 1000 次，每次只见 OLD/NEW 一行
过渡表视角：
    old_rows（before-image）  = 1000 行，改动前的值
    new_rows（after-image）   = 1000 行，改动后的值
    ★ 一个 AFTER 触发器函数被调用 1 次，可以一次看到全部 1000 行

过渡表不是真实表，而是只在触发器函数执行期间有效的只读行集合，函数结束后即消失，无法在函数外引用。

可用性规则 ​

请求项语义只允许用于
OLD TABLE AS name本语句更新或删除的行的旧镜像UPDATE 或 DELETE 事件
NEW TABLE AS name本语句插入或更新的行的新镜像INSERT 或 UPDATE 事件

硬性限制（创建时就会校验）：

  • 只能用于 AFTER 触发器；
  • 不能用于约束触发器（CONSTRAINT TRIGGER）；
  • OLD TABLE / NEW TABLE 各自最多只能声明一次；
  • 声明过渡表时不能同时用 OR 连接多个事件 —— 一个触发器只能对应一个事件；
  • UPDATE 触发器请求过渡表时不能带 UPDATE OF col 列名列表（列名列表本身也不允许这种写法）；
  • 视图上不可用；
  • 当前不能在分区表或继承子表上定义带过渡表的行级触发器。
sql
-- 正确：单一事件 + AFTER + 过渡表
CREATE TRIGGER trg_bulk_audit
  AFTER UPDATE ON products
  REFERENCING OLD TABLE AS old_rows NEW TABLE AS new_rows
  FOR EACH STATEMENT EXECUTE FUNCTION bulk_audit();
sql
-- 错误！请求过渡表时不能合并多个事件
CREATE TRIGGER trg_bad
  AFTER INSERT OR DELETE ON products
  REFERENCING NEW TABLE AS new_rows OLD TABLE AS old_rows
  FOR EACH STATEMENT EXECUTE FUNCTION f();
sql
-- 错误！INSERT 事件没有 OLD TABLE
CREATE TRIGGER trg_bad
  AFTER INSERT ON products
  REFERENCING OLD TABLE AS old_rows
  FOR EACH STATEMENT EXECUTE FUNCTION f();

事件与过渡表组合速查 ​

事件OLD TABLENEW TABLE说明
INSERT不允许允许只有新行
UPDATE允许允许两张表按行一一对应（同一行序）
DELETE允许不允许只有旧行
UPDATE OF col不允许不允许请求过渡表时不能带列名列表

语法与用法 ​

语法位置 ​

text
CREATE [ OR REPLACE ] TRIGGER name
    { BEFORE | AFTER | INSTEAD OF }
    { INSERT | UPDATE | DELETE }          -- 只能一个事件
    ON table_name
    [ REFERENCING { { OLD | NEW } TABLE [ AS ] transition_relation_name } [ ... ] ]
    [ FOR [ EACH ] { ROW | STATEMENT } ]
    [ WHEN ( condition ) ]
    EXECUTE { FUNCTION | PROCEDURE } function_name ( arguments )

AS 关键字可省略，但建议保留以便阅读：

sql
REFERENCING OLD TABLE AS old_rows NEW TABLE AS new_rows

完整示例（取自官方文档结构） ​

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, o.* FROM old_rows o;
  ELSIF (TG_OP = 'UPDATE') THEN
    INSERT INTO emp_audit
    SELECT 'U', now(), current_user, n.* FROM new_rows n;
  ELSIF (TG_OP = 'INSERT') THEN
    INSERT INTO emp_audit
    SELECT 'I', now(), current_user, n.* FROM new_rows n;
  END IF;
  RETURN NULL;
END;
$$;

-- 必须按事件拆成三个触发器
CREATE TRIGGER emp_audit_insert
  AFTER INSERT ON emp
  REFERENCING NEW TABLE AS new_rows
  FOR EACH STATEMENT EXECUTE FUNCTION process_emp_audit();

CREATE TRIGGER emp_audit_update
  AFTER UPDATE ON emp
  REFERENCING NEW TABLE AS new_rows
  FOR EACH STATEMENT EXECUTE FUNCTION process_emp_audit();

CREATE TRIGGER emp_audit_delete
  AFTER DELETE ON emp
  REFERENCING OLD TABLE AS old_rows
  FOR EACH STATEMENT EXECUTE FUNCTION process_emp_audit();

官方对此的评述值得记住：这在触发语句修改了很多行时，可能比行触发器方式快得多；同时「必须为每种事件单独声明触发器」并不妨碍我们复用同一个函数（不过实践中拆成三个独立函数、避免运行时判 TG_OP 会更好）。

使用前的固定判断模板 ​

sql
CREATE OR REPLACE FUNCTION bulk_handler() RETURNS trigger
LANGUAGE plpgsql AS $$
DECLARE
  v_n bigint;
BEGIN
  CASE TG_OP
    WHEN 'INSERT' THEN
      INSERT INTO audit_tbl (...) SELECT ... FROM new_rows;
    WHEN 'UPDATE' THEN
      INSERT INTO audit_tbl (...) SELECT ... FROM new_rows;   -- 需要旧值时 JOIN old_rows
    WHEN 'DELETE' THEN
      INSERT INTO audit_tbl (...) SELECT ... FROM old_rows;
  END CASE;

  GET DIAGNOSTICS v_n = ROW_COUNT;
  RAISE NOTICE '批量处理 % 行', v_n;
  RETURN NULL;
END;
$$;

详细说明 ​

过渡表列结构 ​

过渡表的列结构与触发器所属表的列结构一致，因此可以直接 SELECT * 或在列上做运算：

sql
-- new_rows 的列与 products 相同
INSERT INTO audit_tbl (sku, price)
SELECT sku, price FROM new_rows;

在继承子表场景下要注意：行镜像只包含触发器所属表上存在的列。也就是说，如果触发器建在父表上，而子表多出了自己的列，过渡表里不会有那些子表独有的列。

UPDATE 中 old_rows 与 new_rows 的对应关系 ​

UPDATE 事件的 OLD TABLE 与 NEW TABLE 是一一对应的：同一行在两张表里具有相同的行序，可以按行号或按主键 JOIN 起来做差异比较。

sql
-- 找出本次批量更新中价格下降的行
SELECT o.sku, o.price AS old_price, n.price AS new_price,
       o.price - n.price AS drop_amount
  FROM old_rows o
  JOIN new_rows n ON n.sku = o.sku
 WHERE n.price < o.price;
sql
-- 也可以用行序号对应（过渡表没有显式行号，只能用业务主键或 ctid 类列做关联）
-- 实务上推荐业务主键 JOIN

用 JOIN 而不是位置

虽然有行序保证，但依赖位置对齐的可读性与稳健性都差。用业务主键 JOIN 更明确，也是唯一容易写对的方式。

过渡表与行级触发器的组合 ​

AFTER ROW 触发器也可以请求过渡表，从而「既看当前行，又看到全批改动」：

sql
CREATE TRIGGER trg_row_with_transition
  AFTER UPDATE ON products
  REFERENCING OLD TABLE AS old_rows NEW TABLE AS new_rows
  FOR EACH ROW EXECUTE FUNCTION row_with_batch_view();
sql
CREATE OR REPLACE FUNCTION row_with_batch_view() RETURNS trigger
LANGUAGE plpgsql AS $$
DECLARE
  v_batch_delta numeric;
BEGIN
  -- 当前这一行的新旧值
  RAISE NOTICE '本行 % : % → %', NEW.sku, OLD.price, NEW.price;

  -- 整批的净变化（每行都会重复算一次，注意性能）
  SELECT sum(n.price - o.price) INTO v_batch_delta
    FROM new_rows n JOIN old_rows o USING (sku);

  RAISE NOTICE '整批净变化 = %', v_batch_delta;
  RETURN NULL;
END;
$$;

行级 + 过渡表的代价

行级触发器每行被调用一次，如果函数里又对整批过渡表做聚合，就变成「N 次全批扫描」。这个组合只在确有必要时使用。

另外有一条硬限制：当前不能在分区表或继承子表上定义带过渡表的行级触发器。

外键级联动作与过渡表的交互 ​

外键的引用动作（ON UPDATE CASCADE、ON DELETE SET NULL 等）被视作触发它的那条 SQL 命令的一部分（且永远不会被延迟），因此受影响表上的相关触发器也会触发。

text
DELETE FROM parent WHERE ...;
  └─ ON DELETE CASCADE 对 child 执行内部 DELETE
       └─ child 上的 DELETE 触发器被触发，请求过渡表时能看到这些被级联删除的行

简单情形下，请求过渡表的触发器会把「一条原始 SQL 命令在它表上造成的全部改动」看成一个过渡表。但存在一种复杂情况：当同时存在请求过渡表的 AFTER ROW 触发器时，一条 SQL 命令引发的外键动作可能被拆成多个步骤，每步有各自的过渡表。此时相关的语句级触发器会为每批过渡表各被触发一次，以保证每行在过渡表里恰好出现一次。

sql
-- 混合场景：既有带过渡表的语句级触发器，又有带过渡表的行级触发器
-- → 级联动作可能被分批处理
CREATE TRIGGER trg_stmt AFTER DELETE ON child
  REFERENCING OLD TABLE AS old_rows
  FOR EACH STATEMENT EXECUTE FUNCTION stmt_audit();

CREATE TRIGGER trg_row AFTER DELETE ON child
  REFERENCING OLD TABLE AS old_rows
  FOR EACH ROW EXECUTE FUNCTION row_audit();

ON CONFLICT DO UPDATE 下的过渡表归属 ​

一条带 ON CONFLICT DO UPDATE 的 INSERT 可能同时执行插入与更新操作，因此会触发两类触发器。过渡表是按事件类型隔离的：

sql
INSERT INTO products (sku, name, price)
VALUES ('SKU-001', '苹果', 19.90)
ON CONFLICT (sku) DO UPDATE SET price = EXCLUDED.price;
text
INSERT 触发器看到的 new_rows = 只含「真正被插入的行」
UPDATE 触发器看到的 new_rows = 只含「走 UPDATE 分支的行」
（可能其中一类为空）

这与「语句级触发器一定会执行，但行级触发器只在真的动了行时才执行」是配套的。

视图与过渡表 ​

  • 视图上不能用过渡表；
  • 视图上的语句级 BEFORE / AFTER 触发器只有在视图同时存在行级 INSTEAD OF 触发器时才会被触发；
  • 如果对视图的动作是由 INSTEAD 规则处理的，那么实际执行的是规则展开出来的语句，触发的将是那些替换语句中命名的表上的触发器，视图自身的语句级触发器不触发；
  • 如果视图是自动可更新的，动作会被重写成对基表的操作，触发的是基表的语句级触发器。

分区表与过渡表 ​

text
对分区父表执行 DML：
  语句级触发器 → 只触发「被显式命名的表」上的
  行级触发器   → 在受影响的分区上触发
  过渡表       → 若语句级触发器声明了 REFERENCING，
                 则「所有受影响分区/子表的新旧行镜像都可见」★

这是一条很实用的规则：在分区父表上建带过渡表的语句级触发器，能一次性看到所有分区里被改动的行。相比之下，如果是继承子表场景，行镜像只包含触发器所属表上存在的列。

过渡表能不能写 ​

不能。过渡表是只读的：

sql
-- 错误！不能修改过渡表
UPDATE new_rows SET price = 0;        -- ERROR
DELETE FROM new_rows WHERE ...;       -- ERROR

需要「修正」数据时，改为对目标表本身执行 DML：

sql
-- 正确：基于过渡表计算出需要修正的行，再去更新真实表
UPDATE products p
   SET price = round(p.price, 2)
  FROM new_rows n
 WHERE p.sku = n.sku;

过渡表的作用域 ​

过渡表名只在触发器函数内部可见，其作用域与生命周期都限于本次触发器调用：

sql
-- 触发器函数外引用过渡表名会报错
SELECT * FROM new_rows;    -- ERROR: relation "new_rows" does not exist

若函数里嵌套执行了会再次触发该触发器的 SQL，内层调用有它自己的过渡表集合，不会与外层混淆——但也因此可能出现递归，需要自行防护。

与 RETURNING 的对比 ​

两者都能拿到「本语句影响了哪些行」，但用途不同：

机制面向对象能否写库适用场景
RETURNING客户端否应用侧获取成功写入的行
过渡表触发器函数是服务端批量联动、批量审计
sql
-- 应用侧：用 RETURNING
INSERT INTO products (sku, name, price)
VALUES ('SKU-001', '苹果', 19.90)
RETURNING sku, id;
sql
-- 服务端：用过渡表
CREATE TRIGGER trg_after_insert AFTER INSERT ON products
  REFERENCING NEW TABLE AS new_rows
  FOR EACH STATEMENT EXECUTE FUNCTION after_insert_bulk();

用过渡表替换行级审计的收益量化 ​

以影响 10,000 行的 UPDATE 为例：

方案函数调用次数对审计表的写入操作
AFTER ROW + 逐行 INSERT10,00010,000 条 INSERT
AFTER STATEMENT + 过渡表11 条 INSERT ... SELECT

差异主要来自三块：函数调用与 PL/pgSQL 解释开销、每条 INSERT 的解析/计划/执行开销、以及行级触发器带来的 NEW/OLD 记录构造成本。行数越多，差距越明显。

实战示例 ​

场景：批量价格调整的差异审计 ​

需求：运营每周做一次批量调价，要求记录每一行的前后价格与变动额，同时生成一条汇总记录（总调价幅度、降价商品数）。数据量可能上万行，不能用行级触发器逐行写。

sql
CREATE TABLE products (
  sku      text PRIMARY KEY,
  name     text NOT NULL,
  category text NOT NULL,
  price    numeric(12,2) NOT NULL
);

CREATE TABLE price_change_detail (
  id         bigserial PRIMARY KEY,
  sku        text NOT NULL,
  old_price  numeric(12,2) NOT NULL,
  new_price  numeric(12,2) NOT NULL,
  delta      numeric(12,2) NOT NULL,
  changed_at timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE price_change_summary (
  id           bigserial PRIMARY KEY,
  affected     int NOT NULL,          -- 受影响行数
  increased    int NOT NULL,          -- 涨价行数
  decreased    int NOT NULL,          -- 降价行数
  net_delta    numeric(14,2) NOT NULL,-- 净变动额
  changed_at   timestamptz NOT NULL DEFAULT now()
);

CREATE OR REPLACE FUNCTION price_change_bulk() RETURNS trigger
LANGUAGE plpgsql AS $$
DECLARE
  v_detail_rows bigint;
BEGIN
  -- ① 逐行差异写入明细（一次批量 INSERT）
  INSERT INTO price_change_detail (sku, old_price, new_price, delta)
  SELECT o.sku, o.price, n.price, n.price - o.price
    FROM old_rows o
    JOIN new_rows n ON n.sku = o.sku
   WHERE o.price IS DISTINCT FROM n.price;

  GET DIAGNOSTICS v_detail_rows = ROW_COUNT;

  -- ② 只写入一条汇总
  INSERT INTO price_change_summary (affected, increased, decreased, net_delta)
  SELECT count(*),
         count(*) FILTER (WHERE n.price > o.price),
         count(*) FILTER (WHERE n.price < o.price),
         COALESCE(sum(n.price - o.price), 0)
    FROM old_rows o
    JOIN new_rows n ON n.sku = o.sku
   WHERE o.price IS DISTINCT FROM n.price;

  RAISE NOTICE '调价明细 % 行', v_detail_rows;
  RETURN NULL;
END;
$$;

CREATE TRIGGER trg_price_change_bulk
  AFTER UPDATE ON products
  REFERENCING OLD TABLE AS old_rows NEW TABLE AS new_rows
  FOR EACH STATEMENT EXECUTE FUNCTION price_change_bulk();
text
INSERT INTO products VALUES
  ('SKU-001', '苹果', 'fruit', 20.00),
  ('SKU-002', '香蕉', 'fruit', 10.00),
  ('SKU-003', '白菜', 'veg',    5.00);
INSERT 0 3

-- 一次影响 3 行的批量调价（其中 1 行值未变）
UPDATE products SET price = price * 1.10;
NOTICE:  调价明细 3 行
UPDATE 3

-- 再来一次「伪更新」，值没变 → 仍会触发语句级触发器，但明细为空
UPDATE products SET price = price;
NOTICE:  调价明细 0 行
UPDATE 3

SELECT sku, old_price, new_price, delta FROM price_change_detail ORDER BY id;
   sku   | old_price | new_price |  delta
---------+-----------+-----------+---------
 SKU-001 |     20.00 |     22.00 |    2.00
 SKU-002 |     10.00 |     11.00 |    1.00
 SKU-003 |      5.00 |      5.50 |    0.50
(3 rows)

SELECT affected, increased, decreased, net_delta FROM price_change_summary ORDER BY id;
 affected | increased | decreased | net_delta
----------+-----------+-----------+-----------
        3 |         3 |         0 |      3.50
        0 |         0 |         0 |      0.00
(2 rows)

第二次 UPDATE 3 是典型的「伪更新」：3 行都被写入（影响行数 3），但值没变。过渡表照常提供全部 3 行，靠 WHERE o.price IS DISTINCT FROM n.price 过滤后明细为 0 行——这正是过渡表方案的优势：过滤逻辑写在一条 SQL 里，而不是在 N 次函数调用里各判断一次。

场景：批量删除的引用检查与归档 ​

需求：删除商品时，若这些商品已被订单引用，则一次性抛出包含全部冲突商品的错误消息（而不是删到一半才报错）；没有冲突时，把删除的行批量归档。

由于过渡表只能用于 AFTER，这里采用 AFTER DELETE + 抛错回滚 的方式：AFTER 触发器抛出的异常会让整条 DELETE 与其副作用一起回滚，效果等同于「删除前拦截」，而且能一次列出全部冲突行。

sql
CREATE TABLE product_orders (
  id   bigserial PRIMARY KEY,
  sku  text NOT NULL,
  qty  int  NOT NULL
);

CREATE TABLE products_archive (
  sku         text PRIMARY KEY,
  name        text NOT NULL,
  category    text NOT NULL,
  price       numeric(12,2) NOT NULL,
  archived_at timestamptz NOT NULL DEFAULT now()
);

CREATE OR REPLACE FUNCTION archive_bulk() RETURNS trigger
LANGUAGE plpgsql AS $$
DECLARE
  v_conflict text;
  v_archived bigint;
BEGIN
  -- ① 在整批里一次性找出全部冲突 SKU
  SELECT string_agg(DISTINCT o.sku, ', ' ORDER BY o.sku)
    INTO v_conflict
    FROM old_rows o
   WHERE EXISTS (SELECT 1 FROM product_orders po WHERE po.sku = o.sku);

  IF v_conflict IS NOT NULL THEN
    -- AFTER 阶段的行已删除，但抛错会让整条 DELETE 回滚，等效于「删除前拦截」
    RAISE EXCEPTION '以下商品已被订单引用，无法删除: %', v_conflict
      USING ERRCODE = '23503',
            HINT = '请先删除或改派相关订单明细';
  END IF;

  -- ② 没有冲突，批量归档（一次 INSERT ... SELECT）
  INSERT INTO products_archive (sku, name, category, price)
  SELECT sku, name, category, price FROM old_rows
  ON CONFLICT (sku) DO NOTHING;

  GET DIAGNOSTICS v_archived = ROW_COUNT;
  RAISE NOTICE '已归档 % 个商品', v_archived;
  RETURN NULL;
END;
$$;

CREATE TRIGGER trg_archive_bulk
  AFTER DELETE ON products
  REFERENCING OLD TABLE AS old_rows
  FOR EACH STATEMENT EXECUTE FUNCTION archive_bulk();
text
INSERT INTO products (sku, name, category, price) VALUES
  ('SKU-001', '苹果', 'fruit', 20.00),
  ('SKU-002', '香蕉', 'fruit', 10.00),
  ('SKU-003', '白菜', 'veg',    5.00);

INSERT INTO product_orders (sku, qty) VALUES ('SKU-001', 5);

-- 一次删除 3 行，其中 2 行被订单引用 → 一次性列出全部冲突
DELETE FROM products WHERE price > 0;
ERROR:  以下商品已被订单引用，无法删除: SKU-001, SKU-002
HINT:  请先删除或改派相关订单明细

-- 抛错导致整条 DELETE 回滚，products 与 products_archive 均无变化
SELECT count(*) FROM products;
 count
-------
     3

SELECT count(*) FROM products_archive;
 count
-------
     0

-- 只删除未被引用的商品
DELETE FROM products WHERE sku = 'SKU-003';
NOTICE:  已归档 1 个商品
DELETE 1

SELECT sku, name FROM products_archive;
   sku   | name
---------+------
 SKU-003 | 白菜
(1 row)

这里体现了过渡表的另一个价值：在批量 SQL 里做冲突聚合。用行级触发器则只能在遇到第一行冲突时就抛错，错误消息无法列出全部冲突项；而用过渡表可以 string_agg 出完整清单，对批量运维更友好。

场景：批量更新后刷新物化视图/汇总表 ​

需求：sales_fact 每天批量导入，导入后要刷新按天的汇总表。用过渡表可以只重算受影响的日期，而不是整天全量重算。

sql
CREATE TABLE sales_fact (
  id         bigserial PRIMARY KEY,
  sale_date  date NOT NULL,
  product_key int NOT NULL,
  amount     numeric(14,2) NOT NULL,
  units      int NOT NULL
);

CREATE TABLE sales_by_day (
  sale_date date PRIMARY KEY,
  amount    numeric(16,2) NOT NULL,
  units     bigint NOT NULL,
  refreshed_at timestamptz NOT NULL DEFAULT now()
);

CREATE OR REPLACE FUNCTION refresh_sales_by_day() RETURNS trigger
LANGUAGE plpgsql AS $$
DECLARE
  v_days bigint;
BEGIN
  INSERT INTO sales_by_day (sale_date, amount, units, refreshed_at)
  SELECT f.sale_date,
         COALESCE(sum(f.amount), 0),
         COALESCE(sum(f.units), 0),
         now()
    FROM sales_fact f
   WHERE f.sale_date IN (SELECT DISTINCT sale_date FROM new_rows)
   GROUP BY f.sale_date
  ON CONFLICT (sale_date) DO UPDATE
    SET amount = EXCLUDED.amount,
        units  = EXCLUDED.units,
        refreshed_at = EXCLUDED.refreshed_at;

  GET DIAGNOSTICS v_days = ROW_COUNT;
  RAISE NOTICE '刷新了 % 天的汇总', v_days;
  RETURN NULL;
END;
$$;

CREATE TRIGGER trg_refresh_sales_by_day
  AFTER INSERT ON sales_fact
  REFERENCING NEW TABLE AS new_rows
  FOR EACH STATEMENT EXECUTE FUNCTION refresh_sales_by_day();
text
INSERT INTO sales_fact (sale_date, product_key, amount, units) VALUES
  ('2026-09-01', 1, 100.00, 10),
  ('2026-09-01', 2, 200.00, 20),
  ('2026-09-02', 1,  50.00,  5);
NOTICE:  刷新了 2 天的汇总
INSERT 0 3

-- 再补一批同日数据，只重算受影响的那一天
INSERT INTO sales_fact (sale_date, product_key, amount, units) VALUES
  ('2026-09-01', 3, 300.00, 30);
NOTICE:  刷新了 1 天的汇总
INSERT 0 1

SELECT sale_date, amount, units FROM sales_by_day ORDER BY sale_date;
 sale_date  | amount | units
------------+--------+-------
 2026-09-01 | 600.00 |    60
 2026-09-02 |  50.00 |     5
(2 rows)

关键写法是 WHERE f.sale_date IN (SELECT DISTINCT sale_date FROM new_rows)：把「全量重算」收窄为「只为受影响的维度值重算」。这是过渡表最有价值的用法之一。

注意事项 ​

  • 在 BEFORE 触发器上请求过渡表：只允许 AFTER，创建即报错。-- 错误！
    sql
    CREATE TRIGGER t BEFORE DELETE ON x
  REFERENCING OLD TABLE AS old_rows FOR EACH STATEMENT EXECUTE FUNCTION f();  -- ERROR
  • 在约束触发器上请求过渡表：不允许。-- 错误！
    sql
    CREATE CONSTRAINT TRIGGER t AFTER DELETE ON x
  REFERENCING OLD TABLE AS old_rows FOR EACH ROW EXECUTE FUNCTION f();  -- ERROR
  • 请求过渡表时用 OR 连接多个事件：不允许。-- 错误！
    sql
    CREATE TRIGGER t AFTER INSERT OR DELETE ON x
  REFERENCING NEW TABLE AS n OLD TABLE AS o
  FOR EACH STATEMENT EXECUTE FUNCTION f();   -- ERROR
    必须拆成两个触发器。
  • 在 INSERT 事件请求 OLD TABLE：不允许。-- 错误！
    sql
    CREATE TRIGGER t AFTER INSERT ON x REFERENCING OLD TABLE AS o ...;   -- ERROR
  • 在 DELETE 事件请求 NEW TABLE：不允许。-- 错误！
    sql
    CREATE TRIGGER t AFTER DELETE ON x REFERENCING NEW TABLE AS n ...;   -- ERROR
  • 请求过渡表同时用 UPDATE OF col：不允许。-- 错误！
    sql
    CREATE TRIGGER t AFTER UPDATE OF price ON x
  REFERENCING NEW TABLE AS n FOR EACH STATEMENT EXECUTE FUNCTION f();   -- ERROR
  • 试图修改过渡表：它是只读的。-- 错误！
    sql
    UPDATE new_rows SET price = 0;    -- ERROR
  • 在触发器函数之外引用过渡表名：不存在该关系。-- 错误！
    sql
    SELECT * FROM new_rows;    -- ERROR: relation "new_rows" does not exist
  • 同一个过渡表名声明两次：OLD TABLE / NEW TABLE 各最多一次。-- 错误！
  • 在视图上使用过渡表：不支持。
  • 在分区/继承子表的行级触发器上使用过渡表：当前不支持，语句级则可以使用。
  • 行级 + 过渡表里做整批聚合：会退化为 N 次全批扫描。-- 不推荐

最佳实践 ​

  • 大批量审计一律用 AFTER STATEMENT + 过渡表：把 N 次 INSERT 压成 1 次 INSERT ... SELECT。
  • 按事件拆触发器、按事件拆函数：官方明确建议「实践中可能更好的做法是用三个独立函数，避免运行时对 TG_OP 判断」。
    sql
    CREATE TRIGGER t_ins AFTER INSERT ON x REFERENCING NEW TABLE AS new_rows
  FOR EACH STATEMENT EXECUTE FUNCTION on_insert_bulk();
CREATE TRIGGER t_upd AFTER UPDATE ON x REFERENCING OLD TABLE AS old_rows NEW TABLE AS new_rows
  FOR EACH STATEMENT EXECUTE FUNCTION on_update_bulk();
CREATE TRIGGER t_del AFTER DELETE ON x REFERENCING OLD TABLE AS old_rows
  FOR EACH STATEMENT EXECUTE FUNCTION on_delete_bulk();
  • UPDATE 场景优先 JOIN old_rows 与 new_rows 拿差异，不要依赖位置对齐。
    sql
    FROM old_rows o JOIN new_rows n USING (主键)
WHERE o.price IS DISTINCT FROM n.price
  • 汇总/物化数据只为「受影响的维度值」重算：用 WHERE dim IN (SELECT DISTINCT dim FROM new_rows) 收窄范围。
  • 过滤条件写在批量 SQL 里，而不是在 PL/pgSQL 里逐行判断——这正是过渡表方案的价值所在。
  • 过渡表名用统一后缀 old_rows / new_rows：与官方示例一致，便于团队内快速识别。
  • 语句级函数固定 RETURN NULL。
  • 为每个带过渡表的触发器写 COMMENT ON TRIGGER，注明它处理哪个事件、请求了哪张过渡表。
    sql
    COMMENT ON TRIGGER trg_price_change_bulk ON products
  IS 'AFTER UPDATE + 过渡表：批量写入调价明细与汇总';
  • 需要「整批拦截」时，用行级 BEFORE 做校验 + 语句级 AFTER 做批量处理：过渡表帮不上 BEFORE。
  • 在分区父表上用语句级 + 过渡表，它能看到所有受影响分区的行镜像；子表行级触发器则不支持过渡表。
  • 注意外键级联可能把过渡表拆成多批：语句级触发器可能被触发多次，逻辑要保证幂等（用 ON CONFLICT / NOT EXISTS 去重）。
    sql
    INSERT INTO archive_tbl SELECT ... FROM old_rows
ON CONFLICT (主键) DO NOTHING;
  • 用 GET DIAGNOSTICS ... ROW_COUNT 记录批量写入行数，方便与预期对照。
  • 在生产变更前用 EXPLAIN (ANALYZE) 检查批量 SQL 的执行计划，过渡表虽然省了函数调用，但批量 SQL 本身也要走计划器。

下一节

继续学习：触发器函数编写

参考链接 ​