PHP小码农

Appearance

触发事件：INSERT / UPDATE / DELETE / TRUNCATE ​

CREATE TRIGGER 的第二个必填要素是触发事件（event）：在哪些 DML 操作上回调函数。PostgreSQL 支持四类事件——INSERT、UPDATE、DELETE、TRUNCATE，可以用 OR 把多个事件合并到同一个触发器定义里，也可以用 UPDATE OF col1, col2 把触发范围收窄到特定列的改动。事件决定了 OLD / NEW 哪个可用、能否指定列、以及能否与某些时机组合。

前置知识

阅读本节前，建议先了解：触发时机：BEFORE / AFTER / INSTEAD OF

基础概念 ​

四类事件 ​

事件语义可用于行级可用于语句级有 OLD有 NEW
INSERT插入新行是是否（NULL）是
UPDATE修改已有行是是是是
DELETE删除已有行是是是否（NULL）
TRUNCATE清空整表否是否（NULL）否（NULL）

TRUNCATE 是唯一的例外：它不针对任何具体行，因此只能定义语句级触发器。

sql
-- 正确：TRUNCATE 触发器只能是语句级
CREATE TRIGGER trg_after_truncate
  AFTER TRUNCATE ON staging_rows
  FOR EACH STATEMENT EXECUTE FUNCTION log_truncate();

-- 错误！TRUNCATE 没有行级触发器
CREATE TRIGGER trg_bad
  BEFORE TRUNCATE ON staging_rows
  FOR EACH ROW EXECUTE FUNCTION f();

OLD / NEW 的可用性 ​

text
INSERT：  OLD ─────────────►  NEW [可读，BEFORE 中可写]
UPDATE：  OLD [可读] ──────►  NEW [可读，BEFORE 中可写]
DELETE：  OLD [可读] ──────►  NEW ─────────►（无）
TRUNCATE：OLD ────► NEW ────►（都不可用，语句级）

在不适用的场景访问会直接报错：

sql
-- 错误！INSERT 触发器里没有 OLD
CREATE OR REPLACE FUNCTION bad_insert() RETURNS trigger
LANGUAGE plpgsql AS $$
BEGIN
  RAISE NOTICE '%', OLD.id;    -- ERROR: record "old" is not assigned yet
  RETURN NEW;
END;
$$;

条件严格的写法是先判 TG_OP，再决定读谁：

sql
IF TG_OP = 'DELETE' THEN
  v_row := to_jsonb(OLD);
ELSE
  v_row := to_jsonb(NEW);
END IF;

事件与可用对象的组合 ​

对象类型INSERTUPDATEDELETETRUNCATE
普通表可用可用可用可用
分区表 / 继承父表可用可用可用可用
视图仅 INSTEAD OF仅 INSTEAD OF仅 INSTEAD OF不可用
外部表可用（BEFORE/AFTER 行级 + 语句级）同左同左不可用

语法与用法 ​

事件在语法中的位置 ​

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 } ] ...
        [ 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 )

要点：

  1. 事件至少写一个，多个事件用 OR 连接，顺序任意；
  2. UPDATE OF col, ... 中的列只在 UPDATE 事件上有效，不能写成 INSERT OF col；
  3. 同一个触发器里可以混合事件，例如 AFTER INSERT OR UPDATE OR DELETE；
  4. TRUNCATE 可以和 INSERT/UPDATE/DELETE 写在同一触发器里，但那样该触发器只能是语句级。

多事件合并的典型写法 ​

sql
-- 审计：三类行级事件合并，函数内用 TG_OP 区分
CREATE TRIGGER trg_audit_products
  AFTER INSERT OR UPDATE OR DELETE ON products
  FOR EACH ROW EXECUTE FUNCTION audit_any_table();
sql
-- 清表审计：只能语句级，单独定义
CREATE TRIGGER trg_audit_products_truncate
  AFTER TRUNCATE ON products
  FOR EACH STATEMENT EXECUTE FUNCTION audit_truncate();

UPDATE OF 指定列 ​

sql
-- 只在 price 或 discount 出现在 SET 子句里时才触发
CREATE TRIGGER trg_price_changed
  AFTER UPDATE OF price, discount ON products
  FOR EACH ROW EXECUTE FUNCTION log_price_change();

注意 UPDATE OF 判断的是「列出现在 SET 子句中」，而不是「列的值真的变了」：

sql
-- 即使把 price 设成同一个值，触发器依然会触发
UPDATE products SET price = price WHERE sku = 'SKU-001';   -- 触发器触发

-- 而 SET 里没提到 price 时不会触发
UPDATE products SET name = '新名字' WHERE sku = 'SKU-001';  -- 触发器不触发

如果要「值真的变了才处理」，需要在 WHEN 或函数体里再判断一次：

sql
CREATE TRIGGER trg_price_changed
  AFTER UPDATE OF price ON products
  FOR EACH ROW
  WHEN (OLD.price IS DISTINCT FROM NEW.price)
  EXECUTE FUNCTION log_price_change();

详细说明 ​

INSERT 事件 ​

INSERT 触发器在插入新行时触发。行级 BEFORE INSERT 可以向 NEW 里补值——这是最常见的用法，包括补时间戳、补规范化字段、补默认业务字段。

sql
CREATE OR REPLACE FUNCTION fill_insert_defaults() RETURNS trigger
LANGUAGE plpgsql AS $$
BEGIN
  NEW.created_at := COALESCE(NEW.created_at, now());
  NEW.created_by := COALESCE(NEW.created_by, current_user);
  NEW.code       := upper(trim(NEW.code));
  RETURN NEW;
END;
$$;

CREATE TRIGGER trg_fill_insert_defaults
  BEFORE INSERT ON articles
  FOR EACH ROW EXECUTE FUNCTION fill_insert_defaults();

几个容易踩的点：

  • DEFAULT 与 BEFORE INSERT 的先后：DEFAULT 表达式先求值填充进 NEW，随后 BEFORE 触发器才被调用，因此触发器看到的是「已应用默认值」的 NEW，并可以覆盖它。
  • BEFORE INSERT 里没有 OLD：OLD 是未赋值的 record。
  • INSERT ... ON CONFLICT DO UPDATE 的复杂情况：同一行可能先走 BEFORE INSERT 再走 BEFORE UPDATE，且 BEFORE INSERT 的改动会被 BEFORE UPDATE 看到（包括对 EXCLUDED 列的改动）。若触发器不幂等，结果会出乎意料。
sql
-- ON CONFLICT 场景下，INSERT 与 UPDATE 触发器都会被调用
INSERT INTO products (sku, name, price) VALUES ('SKU-001', '苹果', 19.90)
ON CONFLICT (sku) DO UPDATE SET price = EXCLUDED.price;

另外，官方明确指出：带 ON CONFLICT DO UPDATE 的 INSERT 语句，语句级 UPDATE 触发器一定会被触发，无论最终是否真的走了 UPDATE 分支、无论是否影响任何行。

UPDATE 事件 ​

UPDATE 是唯一同时拥有可读 OLD 与可写 NEW 的事件，因此业务上用得最多。

sql
CREATE OR REPLACE FUNCTION track_status_change() RETURNS trigger
LANGUAGE plpgsql AS $$
BEGIN
  IF OLD.status IS DISTINCT FROM NEW.status THEN
    INSERT INTO status_history (entity_id, from_status, to_status, changed_by)
    VALUES (NEW.id, OLD.status, NEW.status, current_user);
  END IF;
  RETURN NEW;
END;
$$;

CREATE TRIGGER trg_track_status_change
  AFTER UPDATE ON tickets
  FOR EACH ROW EXECUTE FUNCTION track_status_change();

关键语义：

  • OLD 是更新前的行，NEW 是即将写入的行（BEFORE 中可改写 NEW）；
  • 未出现在 SET 子句里的列，NEW 与 OLD 相同；
  • OLD 是只读的：OLD.price := 1; 会报错。
    sql
    -- 错误！OLD 不可赋值
OLD.price := 1;    -- ERROR: record "old" is not assignable
  • 主键/分区键变化可能触发「移动分区」语义：分区表上 UPDATE 导致行跨分区移动时，内部按「从原分区 DELETE + 向新分区 INSERT」执行。此时行级 BEFORE UPDATE 与 BEFORE DELETE 在原分区触发，BEFORE INSERT 在目标分区触发；AFTER 层面触发的是 AFTER DELETE 与 AFTER INSERT，不触发 AFTER UPDATE；语句级只触发 UPDATE 相关触发器。

DELETE 事件 ​

DELETE 触发器只有 OLD 可用，且 BEFORE 行级返回非 NULL 才允许删除。

sql
CREATE OR REPLACE FUNCTION archive_before_delete() RETURNS trigger
LANGUAGE plpgsql AS $$
BEGIN
  INSERT INTO orders_archive (id, sku, qty, archived_at)
  VALUES (OLD.id, OLD.sku, OLD.qty, now());
  RETURN OLD;      -- DELETE 触发器的惯用返回值
END;
$$;

CREATE TRIGGER trg_archive_before_delete
  BEFORE DELETE ON orders
  FOR EACH ROW EXECUTE FUNCTION archive_before_delete();

外键级联删除也会触发 DELETE 触发器

如果外键声明了 ON DELETE CASCADE，级联删除是通过引用表上的普通 DELETE 实现的，因此引用表上的 DELETE 触发器会被触发。若该触发器修改或阻止了这些删除，就可能破坏引用完整性。这是触发器编写者需要自己负责的边界。

sql
CREATE TABLE order_items (
  id       bigserial PRIMARY KEY,
  order_id bigint REFERENCES orders(id) ON DELETE CASCADE
);
-- DELETE FROM orders 时，order_items 上的 DELETE 触发器会被触发

TRUNCATE 事件 ​

TRUNCATE 清空整表，不涉及具体行，因此：

  • 只能是 FOR EACH STATEMENT；
  • OLD / NEW 都不可用；
  • 常用于「清表前拦一道」或「清表后记一笔」。
sql
CREATE OR REPLACE FUNCTION guard_truncate() RETURNS trigger
LANGUAGE plpgsql AS $$
BEGIN
  IF TG_TABLE_NAME LIKE 'prod_%' THEN
    RAISE EXCEPTION '禁止 TRUNCATE 生产表 %', TG_TABLE_NAME
      USING ERRCODE = '42501',
            HINT = '请改用 DELETE ... WHERE 条件';
  END IF;
  RETURN NULL;
END;
$$;

CREATE TRIGGER trg_guard_truncate
  BEFORE TRUNCATE ON staging_rows
  FOR EACH STATEMENT EXECUTE FUNCTION guard_truncate();

TRUNCATE 触发器拿不到「被清了多少行」的信息，也无法知道是否有 TRUNCATE ... CASCADE 连带清空了其他表——不过被级联清空的表自身的 TRUNCATE 触发器同样会被触发。

sql
-- parent 有外键被 child 引用；TRUNCATE parent CASCADE 会连带清空 child
-- 此时 child 上的 AFTER TRUNCATE 触发器也会被触发
TRUNCATE parent CASCADE;

多事件合并时如何在函数里区分 ​

TG_OP 是唯一的判别依据，取值恰为 'INSERT' / 'UPDATE' / 'DELETE' / 'TRUNCATE'：

sql
CREATE OR REPLACE FUNCTION op_branch() RETURNS trigger
LANGUAGE plpgsql AS $$
BEGIN
  CASE TG_OP
    WHEN 'INSERT' THEN
      RAISE NOTICE '插入: %', NEW.id;
      RETURN NEW;
    WHEN 'UPDATE' THEN
      RAISE NOTICE '更新: % → %', OLD.id, NEW.id;
      RETURN NEW;
    WHEN 'DELETE' THEN
      RAISE NOTICE '删除: %', OLD.id;
      RETURN OLD;
    WHEN 'TRUNCATE' THEN
      RAISE NOTICE '清表: %', TG_TABLE_NAME;
      RETURN NULL;
    ELSE
      RAISE WARNING '未知操作 %', TG_OP;
      RETURN NULL;
  END CASE;
END;
$$;

TG_OP 与 TG_LEVEL 配合判断

合并了 TRUNCATE 的触发器必然是语句级，函数里可以用 TG_LEVEL = 'STATEMENT' 先分流，避免访问 NEW/OLD。

UPDATE OF 的判断依据 ​

UPDATE OF 的判定发生在语句解析层，依据是 SET 子句里是否显式提到了该列，与值是否变化无关。几种边界情况：

SET 子句UPDATE OF price 是否触发
SET price = 100触发
SET price = price触发（提到就算）
SET name = 'x'不触发
SET name = 'x', price = price触发
SET (name, price) = (...)触发
SET name = 'x' 但表上有 BEFORE 触发器改了 price不触发（不重新判断）
sql
-- 值没变也会触发，若只想处理「真变化」，需再判一次
CREATE TRIGGER trg_of ON products
  AFTER UPDATE OF price
  FOR EACH ROW
  WHEN (OLD.price IS DISTINCT FROM NEW.price)
  EXECUTE FUNCTION f();

UPDATE OF 与视图触发器、分区表配合时，列名必须是触发器所依附的表/视图上的列名。

sql
-- 错误！列不存在时创建即报错
CREATE TRIGGER trg_of ON products
  AFTER UPDATE OF nonexistent_col
  FOR EACH ROW EXECUTE FUNCTION f();

MERGE 与触发事件的关系 ​

MERGE 命令没有专属触发器，它会按自身动作派发到 INSERT / UPDATE / DELETE 触发器上：

text
MERGE 语句
  ├─ WHEN MATCHED THEN UPDATE  → 触发 UPDATE 触发器
  ├─ WHEN MATCHED THEN DELETE  → 触发 DELETE 触发器
  └─ WHEN NOT MATCHED THEN INSERT → 触发 INSERT 触发器

判定规则细读如下：

层级触发条件
语句级只要 MERGE 的动作里声明了该类动作就触发，无论最终是否真的执行了
行级只有真的执行了该行操作才触发
sql
MERGE INTO products p
USING staging_products s ON p.sku = s.sku
WHEN MATCHED THEN UPDATE SET price = s.price         -- 触发语句级 UPDATE 触发器（总是）
WHEN NOT MATCHED THEN INSERT (sku, name, price)      -- 触发语句级 INSERT 触发器（总是）
  VALUES (s.sku, s.name, s.price);

因此可能出现「语句级 UPDATE 触发器触发了，但没有任何行级 UPDATE 触发器触发」的合法情形。

事件与时机/层级的合法组合速查 ​

事件BEFORE 语句级BEFORE 行级AFTER 语句级AFTER 行级INSTEAD OF 行级（视图）
INSERT是是是是是
UPDATE是是是是是
UPDATE OF col是是是是是（列的语义类似 UPDATE OF）
DELETE是是是是是
TRUNCATE是否是否否（视图不支持）

事件与过渡表的关系 ​

过渡表（REFERENCING OLD TABLE AS ... / NEW TABLE AS ...）的可请求范围由事件决定：

事件OLD TABLENEW TABLE
INSERT不可请求（报错）可用
UPDATE可用可用
DELETE可用不可请求（报错）
sql
-- 正确：UPDATE 同时请求两张过渡表
CREATE TRIGGER trg_diff
  AFTER UPDATE ON products
  REFERENCING OLD TABLE AS old_rows NEW TABLE AS new_rows
  FOR EACH STATEMENT EXECUTE FUNCTION diff_rows();
sql
-- 错误！INSERT 没有 OLD TABLE
CREATE TRIGGER trg_bad
  AFTER INSERT ON products
  REFERENCING OLD TABLE AS old_rows
  FOR EACH STATEMENT EXECUTE FUNCTION f();

事件能否被禁用 ​

触发器可以按名称或类别禁用，这在大批量导入时很常用：

命令作用范围
ALTER TABLE t DISABLE TRIGGER name指定触发器
ALTER TABLE t DISABLE TRIGGER USER所有用户定义的触发器
ALTER TABLE t DISABLE TRIGGER ALL含内部约束触发器（需超级用户）
ALTER TABLE t ENABLE TRIGGER name启用
sql
ALTER TABLE big_table DISABLE TRIGGER USER;
COPY big_table FROM '/data/dump.csv' WITH (FORMAT csv);
ALTER TABLE big_table ENABLE TRIGGER USER;

禁用 TRIGGER ALL 的风险

DISABLE TRIGGER ALL 会连外键的内部约束触发器一起禁用，期间可写入破坏引用完整性的数据。除极少数数据修复场景外不要使用。

实战示例 ​

场景：一个审计函数覆盖三张表的增删改 ​

需求：customers、products、orders 三张表都要审计「谁在什么时候对哪一行做了什么」，同时禁不掉 TRUNCATE 的审计。

sql
CREATE TABLE audit_trail (
  id         bigserial PRIMARY KEY,
  schema_name text NOT NULL,
  table_name  text NOT NULL,
  op          text NOT NULL,
  db_user     text NOT NULL,
  at          timestamptz NOT NULL DEFAULT now(),
  row_before  jsonb,
  row_after   jsonb
);

CREATE TABLE customers (
  id    bigserial PRIMARY KEY,
  name  text NOT NULL,
  email text NOT NULL
);

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

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

CREATE OR REPLACE FUNCTION audit_row_change() RETURNS trigger
LANGUAGE plpgsql AS $$
DECLARE
  v_before jsonb;
  v_after  jsonb;
BEGIN
  IF TG_OP IN ('UPDATE', 'DELETE') THEN
    v_before := to_jsonb(OLD);
  END IF;
  IF TG_OP IN ('INSERT', 'UPDATE') THEN
    v_after := to_jsonb(NEW);
  END IF;

  INSERT INTO audit_trail (schema_name, table_name, op, db_user, row_before, row_after)
  VALUES (TG_TABLE_SCHEMA, TG_TABLE_NAME, TG_OP, current_user, v_before, v_after);

  IF TG_OP = 'DELETE' THEN
    RETURN OLD;
  END IF;
  RETURN NEW;
END;
$$;

-- 行级：三个事件合并
CREATE TRIGGER trg_audit_customers
  AFTER INSERT OR UPDATE OR DELETE ON customers
  FOR EACH ROW EXECUTE FUNCTION audit_row_change();

CREATE TRIGGER trg_audit_products
  AFTER INSERT OR UPDATE OR DELETE ON products
  FOR EACH ROW EXECUTE FUNCTION audit_row_change();

CREATE TRIGGER trg_audit_orders
  AFTER INSERT OR UPDATE OR DELETE ON orders
  FOR EACH ROW EXECUTE FUNCTION audit_row_change();

-- 语句级：TRUNCATE 单独定义（无 OLD/NEW）
CREATE OR REPLACE FUNCTION audit_truncate() RETURNS trigger
LANGUAGE plpgsql AS $$
BEGIN
  INSERT INTO audit_trail (schema_name, table_name, op, db_user)
  VALUES (TG_TABLE_SCHEMA, TG_TABLE_NAME, TG_OP, current_user);
  RETURN NULL;
END;
$$;

CREATE TRIGGER trg_audit_orders_truncate
  AFTER TRUNCATE ON orders
  FOR EACH STATEMENT EXECUTE FUNCTION audit_truncate();
text
INSERT INTO customers (name, email) VALUES ('张三', 'z@example.com');
INSERT 0 1

INSERT INTO products VALUES ('SKU-001', '苹果', 19.90);
INSERT 0 1

UPDATE products SET price = 21.90 WHERE sku = 'SKU-001';
UPDATE 1

INSERT INTO orders (sku, qty) VALUES ('SKU-001', 2);
INSERT 0 1

DELETE FROM orders WHERE qty = 2;
DELETE 1

TRUNCATE orders;
TRUNCATE TABLE

SELECT table_name, op, db_user,
       row_before -> 'price' AS old_price,
       row_after  -> 'price' AS new_price
  FROM audit_trail ORDER BY id;
 table_name |   op     | db_user  | old_price | new_price
------------+----------+----------+-----------+-----------
 customers  | INSERT   | postgres |           | 
 products   | INSERT   | postgres |           | 19.90
 products   | UPDATE   | postgres | 19.90     | 21.90
 orders     | INSERT   | postgres |           | 
 orders     | DELETE   | postgres |           | 
 orders     | TRUNCATE | postgres |           | 
(6 rows)

同一个函数服务三张表、三类行级事件，TRUNCATE 因为拿不到行数据而单独用一个语句级触发器。

场景：用 UPDATE OF 把昂贵的联动收窄到必要列 ​

需求：商品价格变化时需要重算该商品在所有价目表中的展示价，这是个昂贵操作；但 products 表频繁因改名、改描述而更新，若不收窄事件会浪费大量计算。

sql
CREATE TABLE price_books (
  id       bigserial PRIMARY KEY,
  book     text NOT NULL,
  sku      text NOT NULL,
  final_price numeric(12,2) NOT NULL
);

-- 昂贵的重算函数
CREATE OR REPLACE FUNCTION recalc_price_books() RETURNS trigger
LANGUAGE plpgsql AS $$
DECLARE
  v_rule price_books_rule%ROWTYPE;
BEGIN
  FOR v_rule IN SELECT * FROM price_books_rule WHERE active LOOP
    UPDATE price_books pb
       SET final_price = round(NEW.price * v_rule.factor, 2)
     WHERE pb.sku = NEW.sku AND pb.book = v_rule.book;

    IF NOT FOUND THEN
      INSERT INTO price_books (book, sku, final_price)
      VALUES (v_rule.book, NEW.sku, round(NEW.price * v_rule.factor, 2));
    END IF;
  END LOOP;
  RETURN NULL;
END;
$$;

CREATE TABLE price_books_rule (
  book    text PRIMARY KEY,
  factor  numeric(6,4) NOT NULL,
  active  boolean NOT NULL DEFAULT true
);

-- 只在 price 列出现在 SET 中时才重算，且要求值确实变了
CREATE TRIGGER trg_recalc_price_books
  AFTER UPDATE OF price ON products
  FOR EACH ROW
  WHEN (OLD.price IS DISTINCT FROM NEW.price)
  EXECUTE FUNCTION recalc_price_books();
text
INSERT INTO price_books_rule VALUES ('零售', 1.0000, true), ('会员', 0.9500, true);

INSERT INTO products VALUES ('SKU-001', '苹果', 20.00);
-- 插入不触发（触发器只挂在 UPDATE OF price 上）

UPDATE products SET name = '红富士苹果' WHERE sku = 'SKU-001';
UPDATE 1
-- 触发器未触发：SET 里没有 price

UPDATE products SET price = 25.00 WHERE sku = 'SKU-001';
UPDATE 1
-- 触发器触发，重算两个价目表

UPDATE products SET price = 25.00 WHERE sku = 'SKU-001';
UPDATE 1
-- 值没变，被 WHEN 条件拦住，函数未执行

SELECT book, sku, final_price FROM price_books ORDER BY book;
  book  |   sku   | final_price
--------+---------+-------------
 会员   | SKU-001 |       23.75
 零售   | SKU-001 |       25.00
(2 rows)

UPDATE OF price 负责「减少不必要的排队」，WHEN (OLD.price IS DISTINCT FROM NEW.price) 负责「排除值未变的伪更新」，两层收窄叠加后，昂贵的重算只在真正需要时执行。

注意事项 ​

  • 在 INSERT 事件里访问 OLD：OLD 未赋值，运行时报错。-- 错误！
    sql
    CREATE TRIGGER t AFTER INSERT ON x FOR EACH ROW EXECUTE FUNCTION f();  -- f 里用 OLD.col 会报错
  • 在 DELETE 事件里访问 NEW：同理，NEW 是 NULL。-- 错误！
    sql
    IF TG_OP = 'DELETE' THEN RETURN NEW; END IF;   -- 会跳过删除
  • 把 TRUNCATE 和行级事件写进同一个触发器：会因层级不合法而创建失败。-- 错误！
    sql
    CREATE TRIGGER t AFTER INSERT OR TRUNCATE ON x FOR EACH ROW ...;   -- ERROR
    正确做法是拆成两个触发器：行级一个、TRUNCATE 语句级一个。
  • 误以为 UPDATE OF col 判断的是「值变化」：它判断的是「列出现在 SET 里」。-- 不推荐
    sql
    UPDATE products SET price = price;   -- 触发器照样触发
    需要值变化判断时必须补 WHEN (OLD.col IS DISTINCT FROM NEW.col)。
  • UPDATE OF 写了不存在的列：创建直接报错。-- 错误！
    sql
    CREATE TRIGGER t AFTER UPDATE OF no_such_col ON x ...;   -- ERROR
  • 用 INSERT OF 语法：不存在这种写法，OF 只属于 UPDATE。-- 错误！
    sql
    CREATE TRIGGER t AFTER INSERT OF name ON x ...;   -- ERROR: syntax error
  • 在视图上建 INSERT/UPDATE/DELETE 事件却用 BEFORE/AFTER 行级：视图行级只能用 INSTEAD OF。-- 错误！
  • 忽略 MERGE 的语句级触发行为：只要 MERGE 的动作里写了 UPDATE，语句级 UPDATE 触发器就会触发，即使一行都没更新。依赖「语句级触发器意味着有行被改」的假设会出错。
  • TRUNCATE ... CASCADE 连带清空其他表时忘了清理联动数据：被级联表的触发器会被触发，但主表触发器拿不到「连带清了谁」的信息。
  • DISABLE TRIGGER ALL 用于大批量导入：会同时禁用约束触发器，导入期间数据可违反引用完整性。-- 危险

最佳实践 ​

  • 一个函数服务多个事件：用 TG_OP 分流，减少函数数量与维护面。
    sql
    CASE TG_OP WHEN 'INSERT' THEN RETURN NEW; WHEN 'DELETE' THEN RETURN OLD; ELSE RETURN NEW; END CASE;
  • 审计类需求把 TRUNCATE 单独建语句级触发器，不要试图与行级事件合并。
  • 昂贵联动务必用 UPDATE OF 收窄事件：把「所有 UPDATE」变成「只有关键列被写时才处理」。
    sql
    CREATE TRIGGER t AFTER UPDATE OF price, cost ON products FOR EACH ROW EXECUTE FUNCTION f();
  • UPDATE OF 一律配 WHEN ... IS DISTINCT FROM：OF 拦的是「语句提到了列」，WHEN 拦的是「值真的变了」，两者叠加才精准。
  • 事件列表按 INSERT OR UPDATE OR DELETE 的固定顺序书写：便于代码审查时快速比对。
  • 审计表里同时记录 row_before 与 row_after：用 to_jsonb(OLD) / to_jsonb(NEW) 保存完整快照，比逐列记录更耐表结构变更。
  • TRUNCATE 触发器优先做「拦截」而非「记录」：清表是危险操作，用 RAISE EXCEPTION 拦住生产表的误操作价值最高。
    sql
    RAISE EXCEPTION '禁止 TRUNCATE %', TG_TABLE_NAME USING ERRCODE = '42501';
  • 外键引用的表上写 DELETE 触发器时，记得级联删除也会触发：不要在 DELETE 触发器里假设「只有显式 DELETE 才进这里」。
  • 大批量导入前评估是否禁用触发器，并对禁用窗口内的数据一致性做补偿校验。
    sql
    ALTER TABLE t DISABLE TRIGGER USER;
-- COPY ...
ALTER TABLE t ENABLE TRIGGER USER;
  • 为每个触发器写清楚它监听哪些事件：用 COMMENT ON TRIGGER 标注，避免后人漏加事件。
    sql
    COMMENT ON TRIGGER trg_recalc_price_books ON products
  IS 'AFTER UPDATE OF price：价格变化时重算各价目表展示价';
  • 在测试里覆盖「零行受影响」的场景：语句级触发器在 0 行时也会执行，行级不会；这是常见误解点。
    sql
    UPDATE products SET price = 1 WHERE sku = 'NOT-EXIST';   -- UPDATE 0，但语句级触发器已执行
  • 优先用 CHECK / 唯一约束代替「拦 INSERT/UPDATE」的触发器：约束是声明式的、可被优化器利用，且不会带来隐式函数调用开销。

下一节

继续学习：行级触发器 FOR EACH ROW

参考链接 ​