PHP小码农

Appearance

触发时机：BEFORE / AFTER / INSTEAD OF ​

定义触发器时必须回答三个问题：在什么事件上触发（INSERT / UPDATE / DELETE / TRUNCATE）、按行还是按语句触发（FOR EACH ROW / FOR EACH STATEMENT）、以及在操作之前还是之后触发（BEFORE / AFTER / INSTEAD OF）。本节专讲第三个问题——时机决定了触发器能否修改数据、返回值是否被采纳、以及它能看到什么样的行状态，是本组文档里最需要吃透的一节。

前置知识

阅读本节前，建议先了解：触发器概述

基础概念 ​

时机在语句生命周期中的位置 ​

一次 UPDATE 从下达到完成，各种触发器的相对顺序如下：

text
客户端发出：UPDATE t SET ... WHERE ...;
      │
      ▼
┌─────────────────────────────────────────────────────────────┐
│ 语句级 BEFORE 触发器            （语句开始前，一次）          │
└─────────────────────────────────────────────────────────────┘
      │
      ▼ 开始逐行处理
┌─────────────────────────────────────────────────────────────┐
│ 行级 BEFORE 触发器（按名称字母序，可链式改行）                │
│   ├─ 返回 NULL    → 跳过本行，后续同行的 BEFORE 不再执行      │
│   └─ 返回非 NULL  → 以返回值作为本行最终要写入的值            │
└─────────────────────────────────────────────────────────────┘
      │
      ▼
┌─────────────────────────────────────────────────────────────┐
│ 实际写行 + 生成列计算（stored generated columns）            │
│   ★ 生成列在 BEFORE 之后、AFTER 之前算出                     │
└─────────────────────────────────────────────────────────────┘
      │
      ▼ 整个语句的所有行都处理完
┌─────────────────────────────────────────────────────────────┐
│ 行级 AFTER 触发器（按名称字母序，每行一次，返回值被忽略）     │
└─────────────────────────────────────────────────────────────┘
      │
      ▼
┌─────────────────────────────────────────────────────────────┐
│ 语句级 AFTER 触发器             （语句结束时，一次）          │
└─────────────────────────────────────────────────────────────┘
      │
      ▼
   （若为可延迟的约束触发器）推迟到 COMMIT 前再执行

关键结论：行级 AFTER 不是在「这一行写完」时立刻执行，而是在整个语句结束时才执行。这一点经常被误解，也直接影响「AFTER 触发器里查表能看到什么」。

时机 × 层级 组合矩阵 ​

时机FOR EACH ROWFOR EACH STATEMENT可用对象
BEFORE可改 NEW、可跳过该行语句开始前执行一次表、外部表、分区表
AFTER行值已定，返回值被忽略语句结束时执行一次表、外部表、分区表
INSTEAD OF唯一允许的层级不允许仅视图

不合法组合在创建时就会报错：

sql
-- 错误！视图上的 BEFORE / AFTER 只能是语句级
CREATE TRIGGER t BEFORE INSERT ON my_view FOR EACH ROW EXECUTE FUNCTION f();

-- 错误！INSTEAD OF 不支持语句级
CREATE TRIGGER t INSTEAD OF INSERT ON my_view FOR EACH STATEMENT EXECUTE FUNCTION f();

-- 错误！INSTEAD OF 只能用于视图，不能用于表
CREATE TRIGGER t INSTEAD OF INSERT ON my_table FOR EACH ROW EXECUTE FUNCTION f();

一句话分工 ​

需求首选时机理由
校验数据合法性BEFORE能在写入前抛错，可用 NEW 直接判断
计算派生列、补默认值BEFORE可以直接改 NEW 并返回
有条件地忽略某行写入BEFORE返回 NULL 即可跳过
写审计日志AFTER此时行值已最终确定，不会记到「被后续触发器改过的中间态」
维护汇总表 / 计数器AFTER需要确认主操作确实发生了
跨表一致性核查AFTER能看到最终值，避免误判
让视图可写INSTEAD OF唯一选择
跨行、跨表的全局约束AFTER + CONSTRAINT可推迟到事务提交时判断

语法与用法 ​

三种时机的语法位置 ​

text
CREATE [ OR REPLACE ] [ CONSTRAINT ] TRIGGER name
    { BEFORE | AFTER | INSTEAD OF }          -- ① 时机
    { INSERT | UPDATE [ OF column_name [, ...] ] | DELETE | TRUNCATE }
        [ OR { INSERT | UPDATE [ OF column_name [, ...] ] | DELETE | TRUNCATE } ] ...
    ON { table_name | view_name }
    [ FROM referenced_table_name ]
    [ NOT DEFERRABLE | [ DEFERRABLE ] [ INITIALLY IMMEDIATE | INITIALLY DEFERRED ] ]
    [ REFERENCING { { OLD | NEW } TABLE [ AS ] transition_relation_name } [ ... ] ]
    [ FOR [ EACH ] { ROW | STATEMENT } ]     -- ② 层级
    [ WHEN ( condition ) ]
    EXECUTE { FUNCTION | PROCEDURE } function_name ( arguments )

时机（①）与层级（②）是两个独立维度，但它们的组合受对象类型约束，见上表。

触发器函数返回值一览 ​

不同时机的返回值语义差异巨大，值得单独成表：

触发器类型返回值是否被采纳返回 NULL 的含义
BEFORE 行级（INSERT/UPDATE）是，作为该行最终值跳过本行操作
BEFORE 行级（DELETE）无直接影响，但必须非 NULL跳过本行删除
AFTER 行级否，被忽略无影响
BEFORE 语句级否，被忽略无影响
AFTER 语句级否，被忽略无影响
INSTEAD OF是，影响 RETURNING 与影响行数表示「未做任何修改」，该行不计数

一个对照示例 ​

sql
CREATE TABLE item (
  id        bigserial PRIMARY KEY,
  name      text,
  price     numeric(10,2),
  name_up   text,                 -- 由 BEFORE 触发器补全
  price_vat numeric(12,2)         -- 由生成列算出
);

-- BEFORE 行级：补全 name_up，并校验价格
CREATE OR REPLACE FUNCTION item_normalize() RETURNS trigger
LANGUAGE plpgsql AS $$
BEGIN
  IF NEW.price IS NULL OR NEW.price < 0 THEN
    RAISE EXCEPTION '价格非法: %', NEW.price USING ERRCODE = '23514';
  END IF;
  NEW.name_up := upper(NEW.name);
  RETURN NEW;                     -- 必须返回，否则该行被跳过
END;
$$;

CREATE TRIGGER trg_item_normalize
  BEFORE INSERT OR UPDATE ON item
  FOR EACH ROW EXECUTE FUNCTION item_normalize();

-- AFTER 行级：审计（返回值被忽略，返回 NULL 即可）
CREATE OR REPLACE FUNCTION item_audit() RETURNS trigger
LANGUAGE plpgsql AS $$
BEGIN
  RAISE NOTICE 'item 发生 % : id=%', TG_OP, COALESCE(NEW.id, OLD.id);
  RETURN NULL;
END;
$$;

CREATE TRIGGER trg_item_audit
  AFTER INSERT OR UPDATE OR DELETE ON item
  FOR EACH ROW EXECUTE FUNCTION item_audit();

详细说明 ​

BEFORE 行级：唯一能改写数据的时机 ​

BEFORE 行级触发器在「某一行即将被操作」的瞬间被调用。此时 NEW 是一个可写的记录变量，直接赋值即可改变最终写入的值：

sql
CREATE OR REPLACE FUNCTION set_updated_at() RETURNS trigger
LANGUAGE plpgsql AS $$
BEGIN
  NEW.updated_at := now();
  NEW.updated_by := current_user;
  RETURN NEW;
END;
$$;

CREATE TRIGGER trg_touch BEFORE UPDATE ON articles
  FOR EACH ROW EXECUTE FUNCTION set_updated_at();

也可以整体替换 NEW，或构造一个全新的行记录返回。但要注意：返回的行必须与目标表结构完全一致，否则运行时报错。

sql
-- 整体替换 NEW 的写法（字段数量/类型必须与表一致）
NEW := ROW(NEW.id, trim(NEW.title), NEW.body, now())::articles;

另外，BEFORE INSERT 触发器可以覆盖 DEFAULT 已生成的值；而对生成列（generated column）的修改会被忽略并覆盖：

sql
-- 在 BEFORE 触发器里改生成列是无效的
NEW.price_vat := 0;   -- 会被随后执行的生成列计算覆盖

BEFORE 的返回值语义 ​

返回值是 BEFORE 触发器最容易被忽略的细节：

sql
-- 返回 NULL → 跳过本行
CREATE OR REPLACE FUNCTION skip_archived() RETURNS trigger
LANGUAGE plpgsql AS $$
BEGIN
  IF NEW.status = 'archived' THEN
    RETURN NULL;      -- 本行不会被插入/更新，后续同行的 BEFORE 触发器也不再执行
  END IF;
  RETURN NEW;
END;
$$;
  • 返回 NULL：跳过当前行的操作，且后续同事件的 BEFORE / INSTEAD OF 触发器不再被执行。
  • 返回非 NULL：操作继续，返回值作为该行的最终值传给下一个触发器或执行器。
  • 忘记 RETURN：PL/pgSQL 函数结尾没有 RETURN 时返回 NULL，效果等同「静默跳过所有行」——这是线上事故的常见成因。
sql
-- 错误！忘记 RETURN，导致所有行都被跳过
CREATE OR REPLACE FUNCTION bad_touch() RETURNS trigger
LANGUAGE plpgsql AS $$
BEGIN
  NEW.updated_at := now();
  -- 少写了 RETURN NEW;
END;
$$;

BEFORE 行级 DELETE 的特殊之处 ​

DELETE 触发器里没有 NEW（它是 NULL），只有 OLD。虽然返回值对删除行为没有直接影响，但仍必须返回非 NULL，否则该行不会被删除：

sql
CREATE OR REPLACE FUNCTION guard_delete() RETURNS trigger
LANGUAGE plpgsql AS $$
BEGIN
  IF OLD.is_locked THEN
    RETURN NULL;        -- 跳过本行删除
  END IF;
  RETURN OLD;           -- 惯用写法：DELETE 触发器返回 OLD
END;
$$;

在 DELETE 触发器里返回 NEW

NEW 在 DELETE 触发器里是 NULL，返回它等价于「跳过本行删除」。这类 bug 不会报错，只会悄悄少删数据。

AFTER：值已落定，但只能抛错不能改值 ​

AFTER 行级触发器在整个语句的所有行都处理完毕后，逐行被调用。此时：

  • 行已经写入（或删除），NEW/OLD 都是最终确定的值；
  • 返回值被完全忽略；
  • 不能修改 NEW（改了也不会生效）；
  • 唯一的干预手段是 RAISE EXCEPTION，它会让整个语句连同触发器自身的副作用一起回滚。
sql
-- AFTER 触发器修改 NEW 是无效操作
CREATE OR REPLACE FUNCTION useless_after() RETURNS trigger
LANGUAGE plpgsql AS $$
BEGIN
  NEW.status := 'checked';   -- 不推荐：毫无效果
  RETURN NEW;                -- 返回值同样被忽略
END;
$$;

由于 AFTER 看到的是最终值，跨表校验只能写在 AFTER：

sql
-- 校验订单明细总额不超过订单头的额度（需要最终值）
CREATE OR REPLACE FUNCTION check_order_total() RETURNS trigger
LANGUAGE plpgsql AS $$
DECLARE
  v_total numeric;
  v_limit numeric;
BEGIN
  SELECT sum(amount), max(o.credit_limit)
    INTO v_total, v_limit
    FROM order_items i
    JOIN orders o ON o.id = i.order_id
   WHERE i.order_id = NEW.order_id
   GROUP BY o.id;

  IF v_total > v_limit THEN
    RAISE EXCEPTION '订单 % 总额 % 超出额度 %',
      NEW.order_id, v_total, v_limit USING ERRCODE = '23514';
  END IF;
  RETURN NULL;
END;
$$;

AFTER 行级触发器的执行时机

它不是在「本行写完」时执行，而是在语句末尾执行。因此在 AFTER 触发器里 SELECT 目标表，能看到本条语句影响的全部行（因为都已写入）。如果误以为它是「逐行即时执行」，很容易写出错误的聚合逻辑。

语句级 BEFORE / AFTER ​

语句级触发器与具体行无关，NEW/OLD 在其中都是 NULL：

类型执行时刻典型用途
BEFORE ... FOR EACH STATEMENT语句开始前，一次准备临时数据、记录开始时间、检查前置条件
AFTER ... FOR EACH STATEMENT语句结束后，一次批量审计（配过渡表）、刷新汇总、发通知
sql
-- 语句级 AFTER：统计总影响行数（用 GET DIAGNOSTICS 拿不到跨触发器行数，此处演示计时）
CREATE OR REPLACE FUNCTION log_bulk_load() RETURNS trigger
LANGUAGE plpgsql AS $$
BEGIN
  -- NEW/OLD 在语句级触发器里是 NULL，访问会报错
  RAISE NOTICE '批量导入结束: op=% table=%',
    TG_OP, TG_TABLE_SCHEMA || '.' || TG_TABLE_NAME;
  RETURN NULL;
END;
$$;

CREATE TRIGGER trg_log_bulk_load
  AFTER INSERT ON staging_rows
  FOR EACH STATEMENT EXECUTE FUNCTION log_bulk_load();

语句级 AFTER 总是排在行级 AFTER 之后，这是官方定义的固定次序。

INSTEAD OF：视图的可写代理 ​

INSTEAD OF 触发器只用于视图，且只允许行级。它的语义不是「在操作之前/之后」，而是「取代这个操作」——视图本身没有可写存储，真正要改的是底层基表：

sql
CREATE TABLE emp (
  empname text PRIMARY KEY,
  salary  integer
);

CREATE TABLE emp_audit (
  operation char(1) NOT NULL,
  userid    text NOT NULL,
  empname   text NOT NULL,
  salary    integer,
  stamp     timestamp NOT NULL
);

CREATE VIEW emp_view AS
  SELECT e.empname, e.salary, max(ea.stamp) AS last_updated
    FROM emp e
    LEFT JOIN emp_audit ea ON ea.empname = e.empname
   GROUP BY 1, 2;

CREATE OR REPLACE FUNCTION update_emp_view() RETURNS trigger
LANGUAGE plpgsql AS $$
BEGIN
  IF (TG_OP = 'DELETE') THEN
    DELETE FROM emp WHERE empname = OLD.empname;
    IF NOT FOUND THEN
      RETURN NULL;               -- 没删到任何行 → 本行不计入影响行数
    END IF;
    OLD.last_updated := now();
    INSERT INTO emp_audit VALUES ('D', current_user, OLD.*);
    RETURN OLD;
  ELSIF (TG_OP = 'UPDATE') THEN
    UPDATE emp SET salary = NEW.salary WHERE empname = OLD.empname;
    IF NOT FOUND THEN
      RETURN NULL;
    END IF;
    NEW.last_updated := now();
    INSERT INTO emp_audit VALUES ('U', current_user, NEW.*);
    RETURN NEW;
  ELSIF (TG_OP = 'INSERT') THEN
    INSERT INTO emp VALUES (NEW.empname, NEW.salary);
    NEW.last_updated := now();
    INSERT INTO emp_audit VALUES ('I', current_user, NEW.*);
    RETURN NEW;
  END IF;
  RETURN NULL;
END;
$$;

CREATE TRIGGER emp_audit
  INSTEAD OF INSERT OR UPDATE OR DELETE ON emp_view
  FOR EACH ROW EXECUTE FUNCTION update_emp_view();

INSTEAD OF 的返回值有明确含义：

  • 返回 NULL：声明「我没做任何改动」，该行不计入 INSERT/UPDATE/DELETE 的影响行数；
  • 返回非 NULL：声明「操作已完成」，影响行数 +1；
  • 对 INSERT/UPDATE，返回的 NEW 会决定 RETURNING 输出什么（可以在返回前修改 NEW）。

视图上的语句级触发器有个前提

视图上可以定义语句级 BEFORE/AFTER 触发器，但它们只有在视图同时存在 INSTEAD OF 触发器时才会被触发。否则对视图的语句会被改写到基表，触发的是基表上的触发器。

INSTEAD OF 不支持 WHEN 条件

想按条件决定是否代写，只能在函数体里写 IF 判断。

约束触发器：把 AFTER 推迟到提交 ​

在 CREATE TRIGGER 前加 CONSTRAINT 关键字，得到约束触发器。它必须是 AFTER ROW，并且可以声明为可延迟，从而在 COMMIT 前才逐个检查：

sql
CREATE CONSTRAINT TRIGGER trg_budget_check
  AFTER INSERT OR UPDATE ON ledger_entries
  DEFERRABLE INITIALLY DEFERRED
  FOR EACH ROW
  EXECUTE FUNCTION check_daily_budget_later();

延迟的意义在于：事务中间态可能暂时违反约束，只要提交时满足即可。比如「先插入明细行、后更新汇总行」的两步操作，用即时检查会误报。

sql
-- 事务内可临时切换延迟/立即
SET CONSTRAINTS trg_budget_check IMMEDIATE;   -- 立刻检查（用于调试）
SET CONSTRAINTS ALL DEFERRED;                 -- 全部推迟到提交

约束触发器可以配置为不延迟（NOT DEFERRABLE），此时行为与普通 AFTER ROW 触发器一致。

生成列与时机的关系 ​

存储生成列（GENERATED ALWAYS AS (...) STORED）的计算发生在 BEFORE 触发器之后、AFTER 触发器之前：

text
BEFORE 触发器（改 NEW 的普通列）
      ↓
生成列计算（根据最终的普通列算出）
      ↓
AFTER 触发器（能读到生成列的值）★

由此得到两条使用规则：

sql
-- 正确：AFTER 触发器里可以放心读生成列
CREATE OR REPLACE FUNCTION read_generated() RETURNS trigger
LANGUAGE plpgsql AS $$
BEGIN
  IF NEW.total_with_tax > 100000 THEN
    RAISE WARNING '金额异常: %', NEW.total_with_tax;
  END IF;
  RETURN NULL;
END;
$$;
sql
-- 不推荐：BEFORE 触发器里 NEW 的生成列尚未计算，值是未定义的
CREATE OR REPLACE FUNCTION read_generated_early() RETURNS trigger
LANGUAGE plpgsql AS $$
BEGIN
  RAISE NOTICE '%', NEW.total_with_tax;   -- 不要这样写
  RETURN NEW;
END;
$$;

虚拟生成列（virtual generated column）在触发器执行时永远不会被计算，触发器函数中不应访问。

时机与 WHEN 条件的求值点 ​

WHEN 条件的求值时机随时机不同而不同，这直接影响性能：

触发器类型WHEN 求值时机不满足时的代价
BEFORE紧接在函数可能执行之前省下一次函数调用
AFTER行更新刚完成时不排队该事件，语句结束时不需回查该行
INSTEAD OF不支持—

对 AFTER 触发器来说，WHEN 的收益远大于 BEFORE：

sql
-- AFTER + WHEN：只有价格真的变了才排队审计，大批量 UPDATE 下提速明显
CREATE TRIGGER trg_price_audit
  AFTER UPDATE ON products
  FOR EACH ROW
  WHEN (OLD.price IS DISTINCT FROM NEW.price)
  EXECUTE FUNCTION audit_price_change();
sql
-- 等价但不推荐：把判断挪进函数体，事件仍会被排队
CREATE TRIGGER trg_price_audit
  AFTER UPDATE ON products
  FOR EACH ROW
  EXECUTE FUNCTION audit_price_change();  -- 函数体里再判断，白排队了

触发器执行顺序与链式传递 ​

同一个事件上定义多个触发器时：

  • 按触发器名称的字母序执行，与创建先后无关；
  • BEFORE 与 INSTEAD OF 的返回值会链式传递给下一个触发器；
  • 任何一个 BEFORE/INSTEAD OF 返回 NULL，该行的后续触发器全部不再执行。
sql
-- 字母序：trg_01_* → trg_02_* → trg_03_*
CREATE TRIGGER trg_01_trim    BEFORE INSERT ON users FOR EACH ROW EXECUTE FUNCTION f1();
CREATE TRIGGER trg_02_lower   BEFORE INSERT ON users FOR EACH ROW EXECUTE FUNCTION f2();
CREATE TRIGGER trg_03_validate BEFORE INSERT ON users FOR EACH ROW EXECUTE FUNCTION f3();

用等宽数字前缀命名，是把「执行顺序」变成显式契约的最简单办法。

时机选择的经验法则 ​

text
需要修改行数据？          → BEFORE 行级
需要直接拒绝某一行？      → BEFORE 行级返回 NULL
需要看到最终值？          → AFTER
需要跨行/跨表校验？        → AFTER（必要时加 CONSTRAINT 延迟）
需要让视图可写？          → INSTEAD OF
没有特别理由？            → BEFORE 更省（无需把事件排到语句末尾）

官方文档对此的表述很直接：如果没有特定理由选择 AFTER，BEFORE 更高效，因为操作信息不必保留到语句末尾。

实战示例 ​

场景：库存扣减的 BEFORE 校验 + AFTER 告警 ​

需求：stock_moves 记录出入库流水，要求：

  1. 出库数量不能超过当前库存（BEFORE 校验，写入前拦住）；
  2. 写入后若某 SKU 库存低于安全线，记一条告警（AFTER，此时库存已是最终值）。
sql
CREATE TABLE inventory_sku (
  sku         text PRIMARY KEY,
  on_hand     int NOT NULL DEFAULT 0,
  safety_line int NOT NULL DEFAULT 0
);

CREATE TABLE stock_moves (
  id        bigserial PRIMARY KEY,
  sku       text NOT NULL REFERENCES inventory_sku(sku),
  qty       int  NOT NULL,          -- 正数入库，负数出库
  moved_at  timestamptz NOT NULL DEFAULT now(),
  note      text
);

CREATE TABLE stock_alerts (
  id       bigserial PRIMARY KEY,
  sku      text NOT NULL,
  on_hand  int  NOT NULL,
  at       timestamptz NOT NULL DEFAULT now()
);

-- BEFORE 行级：校验出库量
CREATE OR REPLACE FUNCTION stock_move_check() RETURNS trigger
LANGUAGE plpgsql AS $$
DECLARE
  v_on_hand int;
BEGIN
  IF NEW.qty = 0 THEN
    RAISE EXCEPTION '库存变动数量不能为 0'
      USING ERRCODE = '23514', COLUMN = 'qty', TABLE = 'stock_moves';
  END IF;

  SELECT on_hand INTO v_on_hand
    FROM inventory_sku WHERE sku = NEW.sku FOR UPDATE;

  IF NEW.qty < 0 AND v_on_hand + NEW.qty < 0 THEN
    RAISE EXCEPTION 'SKU % 库存不足：现有 %，需出库 %',
      NEW.sku, v_on_hand, -NEW.qty
      USING ERRCODE = '23514', COLUMN = 'qty';
  END IF;

  RETURN NEW;
END;
$$;

CREATE TRIGGER trg_stock_move_check
  BEFORE INSERT ON stock_moves
  FOR EACH ROW EXECUTE FUNCTION stock_move_check();

-- BEFORE 行级：同步库存
CREATE OR REPLACE FUNCTION stock_move_apply() RETURNS trigger
LANGUAGE plpgsql AS $$
BEGIN
  UPDATE inventory_sku
     SET on_hand = on_hand + NEW.qty
   WHERE sku = NEW.sku;
  RETURN NEW;
END;
$$;

CREATE TRIGGER trg_stock_move_apply
  BEFORE INSERT ON stock_moves
  FOR EACH ROW EXECUTE FUNCTION stock_move_apply();

-- AFTER 行级：低库存告警（读到的是最终库存）
CREATE OR REPLACE FUNCTION stock_low_alert() RETURNS trigger
LANGUAGE plpgsql AS $$
DECLARE
  v_on_hand int;
  v_line    int;
BEGIN
  SELECT on_hand, safety_line INTO v_on_hand, v_line
    FROM inventory_sku WHERE sku = NEW.sku;

  IF v_on_hand < v_line THEN
    INSERT INTO stock_alerts (sku, on_hand) VALUES (NEW.sku, v_on_hand);
  END IF;
  RETURN NULL;    -- AFTER 触发器返回值被忽略
END;
$$;

CREATE TRIGGER trg_stock_low_alert
  AFTER INSERT ON stock_moves
  FOR EACH ROW EXECUTE FUNCTION stock_low_alert();
text
INSERT INTO inventory_sku VALUES ('SKU-A', 100, 20);

INSERT INTO stock_moves (sku, qty) VALUES ('SKU-A', 50);
INSERT 0 1

INSERT INTO stock_moves (sku, qty) VALUES ('SKU-A', -55);
ERROR:  SKU SKU-A 库存不足：现有 150，需出库 55
-- （上一条已把库存加到 150，此条再出库 55 其实是合法的，示例演示校验路径）

INSERT INTO stock_moves (sku, qty) VALUES ('SKU-A', -200);
ERROR:  SKU SKU-A 库存不足：现有 150，需出库 200

INSERT INTO stock_moves (sku, qty) VALUES ('SKU-A', -140);
INSERT 0 1

SELECT sku, on_hand, safety_line FROM inventory_sku;
  sku  | on_hand | safety_line
-------+---------+-------------
 SKU-A |      10 |          20

SELECT sku, on_hand FROM stock_alerts;
  sku  | on_hand
-------+---------
 SKU-A |      10
(1 row)

注意两个 BEFORE 触发器按名称字母序执行：trg_stock_move_apply 先于 trg_stock_move_check（a < c）——这说明命名顺序即执行顺序，校验与落地操作的先后必须靠命名控制，不能靠猜测。

场景：用 INSTEAD OF 把多表联接视图变成可写接口 ​

需求：给 PHP 应用暴露一个「用户 + 最后登录时间」的视图，应用直接往视图里 INSERT，由触发器分发到底层两张表。

sql
CREATE TABLE app_user (
  id       bigserial PRIMARY KEY,
  username text NOT NULL UNIQUE,
  email    text NOT NULL
);

CREATE TABLE user_login (
  id       bigserial PRIMARY KEY,
  user_id  bigint NOT NULL REFERENCES app_user(id),
  last_ip  inet,
  login_at timestamptz NOT NULL DEFAULT now()
);

CREATE VIEW v_user_login AS
  SELECT u.id, u.username, u.email,
         max(l.login_at) AS last_login_at
    FROM app_user u
    LEFT JOIN user_login l ON l.user_id = u.id
   GROUP BY u.id, u.username, u.email;

CREATE OR REPLACE FUNCTION v_user_login_write() RETURNS trigger
LANGUAGE plpgsql AS $$
DECLARE
  v_id bigint;
BEGIN
  IF (TG_OP = 'INSERT') THEN
    INSERT INTO app_user (username, email)
    VALUES (NEW.username, NEW.email)
    RETURNING id INTO v_id;

    INSERT INTO user_login (user_id, login_at)
    VALUES (v_id, COALESCE(NEW.last_login_at, now()));

    NEW.id := v_id;
    RETURN NEW;                    -- 非 NULL → 计入影响行数，并决定 RETURNING 内容
  ELSIF (TG_OP = 'UPDATE') THEN
    UPDATE app_user
       SET username = NEW.username, email = NEW.email
     WHERE id = OLD.id;
    IF NOT FOUND THEN
      RETURN NULL;                 -- 目标行不存在 → 声明「未做修改」
    END IF;

    INSERT INTO user_login (user_id, login_at)
    VALUES (OLD.id, COALESCE(NEW.last_login_at, now()));
    RETURN NEW;
  ELSIF (TG_OP = 'DELETE') THEN
    DELETE FROM user_login WHERE user_id = OLD.id;
    DELETE FROM app_user   WHERE id = OLD.id;
    IF NOT FOUND THEN
      RETURN NULL;
    END IF;
    RETURN OLD;
  END IF;
  RETURN NULL;
END;
$$;

CREATE TRIGGER trg_v_user_login_write
  INSTEAD OF INSERT OR UPDATE OR DELETE ON v_user_login
  FOR EACH ROW EXECUTE FUNCTION v_user_login_write();
text
INSERT INTO v_user_login (username, email, last_login_at)
VALUES ('alice', 'alice@example.com', '2026-09-01 10:00:00')
RETURNING id, username, last_login_at;
 id | username |    last_login_at
----+----------+---------------------
  1 | alice    | 2026-09-01 10:00:00
(1 row)

INSERT 0 1

UPDATE v_user_login SET email = 'alice@new.com' WHERE username = 'alice';
UPDATE 1

DELETE FROM v_user_login WHERE username = 'alice';
DELETE 1

SELECT count(*) FROM app_user;
 count
-------
     0
(1 row)

RETURNING 能返回正确结果，是因为 INSTEAD OF 触发器返回了非 NULL 的 NEW/OLD。若返回 NULL，会看到 INSERT 0 0，应用侧会误判为「一行都没写成功」。

注意事项 ​

  • BEFORE 行级忘记 RETURN NEW：函数末尾无 RETURN 时返回 NULL，会静默跳过所有行。-- 错误！
    sql
    CREATE FUNCTION f() RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN
  NEW.updated_at := now();
END;              -- 少了 RETURN NEW; → 每次 UPDATE 都是 UPDATE 0
$$;
  • 在 AFTER 触发器里试图修改 NEW：改动不生效，且不会报错。-- 错误！
    sql
    CREATE TRIGGER t AFTER UPDATE ON x FOR EACH ROW EXECUTE FUNCTION f();  -- f 里 NEW.a := 1 无效
  • DELETE 触发器里返回 NEW：NEW 为 NULL，等价于跳过删除。-- 错误！
    sql
    IF TG_OP = 'DELETE' THEN RETURN NEW; END IF;   -- 应返回 OLD
  • 在视图上写 BEFORE/AFTER 行级触发器：视图的行级只能 INSTEAD OF。-- 错误！
    sql
    CREATE TRIGGER t BEFORE INSERT ON v FOR EACH ROW ...;          -- ERROR
CREATE TRIGGER t INSTEAD OF INSERT ON v FOR EACH ROW ...;      -- 正确
  • 在语句级触发器里访问 NEW/OLD：它们是 NULL，运行时报 record "new" is not assigned yet。-- 错误！
    sql
    CREATE TRIGGER t AFTER INSERT ON x FOR EACH STATEMENT EXECUTE FUNCTION f();  -- f 里用 NEW.col 会报错
  • 在视图上建语句级触发器却没建 INSTEAD OF：该语句级触发器不会被触发。-- 不推荐
    sql
    -- 单独存在时形同虚设
CREATE TRIGGER t AFTER INSERT ON v FOR EACH STATEMENT EXECUTE FUNCTION f();
  • 依赖「行级 AFTER 是逐行即时执行」的错误假设：它在语句末尾才跑，此时表里已有全部行的最终值，聚合结果与直觉可能不同。
  • INSTEAD OF 与 WHEN 搭配：不支持，创建即报错。-- 错误！
    sql
    CREATE TRIGGER t INSTEAD OF INSERT ON v FOR EACH ROW
  WHEN (NEW.a > 0) EXECUTE FUNCTION f();    -- ERROR
  • TRUNCATE 上建行级触发器：TRUNCATE 只能语句级。-- 错误！
    sql
    CREATE TRIGGER t BEFORE TRUNCATE ON x FOR EACH ROW ...;   -- ERROR
  • 写生成列：BEFORE 触发器对存储生成列的赋值会被覆盖；虚拟生成列在触发器中未计算，不可访问。-- 错误！

最佳实践 ​

  • 能用 BEFORE 就用 BEFORE：没有明确理由时它更省，因为无需把事件信息保留到语句末尾。
    sql
    CREATE TRIGGER t BEFORE UPDATE ON x FOR EACH ROW EXECUTE FUNCTION f();
  • 校验放 BEFORE，联动放 AFTER：BEFORE 只保证「本行此刻合法」，AFTER 才能看到最终值。
  • BEFORE 函数结尾固定写 RETURN NEW（DELETE 写 RETURN OLD），把它当作模板强制项。
    sql
    CREATE OR REPLACE FUNCTION f() RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN
  -- ... 逻辑 ...
  IF TG_OP = 'DELETE' THEN RETURN OLD; END IF;
  RETURN NEW;
END;
$$;
  • AFTER 触发器务必配 WHEN：能在排队阶段就跳过大部分行，大批量 DML 下收益显著。
    sql
    CREATE TRIGGER t AFTER UPDATE ON x FOR EACH ROW
  WHEN (OLD.v IS DISTINCT FROM NEW.v) EXECUTE FUNCTION f();
  • 用等宽数字前缀控制执行顺序：trg_01_、trg_02_，让顺序成为可读的契约。
  • AFTER 里不要做重活：它处在语句临界路径上，慢查询会成倍放大；批量逻辑改用语句级 + 过渡表。
  • 跨表一致性检查优先用 AFTER + 延迟约束触发器：允许事务中间态，只在提交前判定。
    sql
    CREATE CONSTRAINT TRIGGER t AFTER INSERT ON ledger
  DEFERRABLE INITIALLY DEFERRED FOR EACH ROW EXECUTE FUNCTION f();
  • 视图可写化统一用 INSTEAD OF，并在 INSERT/UPDATE 分支返回 NEW、DELETE 分支返回 OLD，保证 RETURNING 与影响行数正确。
  • INSTEAD OF 里没改动任何数据时显式返回 NULL，让影响行数如实反映结果。
  • 生成列相关逻辑只写在 AFTER，BEFORE 里不要读取生成列。
  • 把「时机」写进注释与文档：用 COMMENT ON TRIGGER 记录为什么选 BEFORE 或 AFTER，避免后人误改。
    sql
    COMMENT ON TRIGGER trg_stock_move_check ON stock_moves
  IS 'BEFORE：写入前校验库存充足，不合法直接抛 23514';
  • 用事务回滚测试时机行为：起一个事务、执行 DML、观察 NOTICE 与副作用，再 ROLLBACK，可重复验证顺序与返回值语义。
    sql
    BEGIN;
UPDATE products SET price = 1 WHERE sku = 'SKU-001';
ROLLBACK;

下一节

继续学习：INSERT / UPDATE / DELETE / TRUNCATE 触发事件

参考链接 ​