45. 触发器（TRIGGER）与存储过程（PROCEDURE）

本节目标：理解触发器如何自动响应增删改，并掌握存储过程与函数的区别及 CALL 调用、过程内的事务控制。

触发器是什么

触发器（TRIGGER）是绑在表上的”自动反应”。当表发生 INSERT、UPDATE 或 DELETE 时，它会自动执行一段函数。

典型用途：写审计日志、自动算派生字段、校验数据。触发器是”事件驱动”的，你不用手动调用它。

触发器函数与特殊变量

触发器必须先有一个返回 trigger 类型的函数。函数里能用几个特殊变量：

NEW：新插入 / 更新后的那一行。
OLD：被更新 / 删除前的那一行。
TG_OP：触发事件，‘INSERT’ / ‘UPDATE’ / ‘DELETE’。
TG_TABLE_NAME：被触发的表名。

postgres=# CREATE OR REPLACE FUNCTION log_order_change()
postgres=# RETURNS trigger
postgres=# LANGUAGE plpgsql
postgres=# AS $$
postgres=# BEGIN
postgres=#   INSERT INTO order_log(order_id, happened_at)
postgres=#   VALUES (NEW.id, now());
postgres=#   RETURN NEW;
postgres=# END;
postgres=# $$;

先准备日志表：

postgres=# CREATE TABLE order_log (
postgres=#   id         INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
postgres=#   order_id   INT,
postgres=#   happened_at TIMESTAMP DEFAULT now()
postgres=# );

绑定触发器

postgres=# CREATE TRIGGER trg_after_order_insert
postgres=# AFTER INSERT ON orders
postgres=# FOR EACH ROW
postgres=# EXECUTE FUNCTION log_order_change();

BEFORE / AFTER：在事件前还是后触发。
FOR EACH ROW：每行触发一次；也可 FOR EACH STATEMENT 整条语句触发一次。
NEW 在 BEFORE 触发器里改了值，会影响最终写入的数据。

Tip

做数据校验用 BEFORE，发现不合法直接报错；做日志记录用 AFTER，数据已落盘更稳。

BEFORE 触发器修改数据

BEFORE 触发器最常见的玩法：写入前自动规范数据。比如把用户名去空格转小写、邮箱统一小写：

postgres=# CREATE OR REPLACE FUNCTION normalize_user()
postgres=# RETURNS trigger
postgres=# LANGUAGE plpgsql
postgres=# AS $$
postgres=# BEGIN
postgres=#   NEW.username := lower(trim(NEW.username));
postgres=#   NEW.email    := lower(NEW.email);
postgres=#   RETURN NEW;
postgres=# END;
postgres=# $$;

postgres=# CREATE TRIGGER trg_before_user
postgres=# BEFORE INSERT OR UPDATE ON users
postgres=# FOR EACH ROW EXECUTE FUNCTION normalize_user();

注意：BEFORE 触发器必须 RETURN NEW（或 RETURN NULL 表示放弃这行），AFTER 触发器返回值会被忽略，习惯上 RETURN NULL。

带条件的触发器 WHEN

只在大额订单时才记日志，用 WHEN 条件过滤，省去在函数里写 IF：

postgres=# CREATE TRIGGER trg_log_big_order
postgres=# AFTER INSERT ON orders
postgres=# FOR EACH ROW
postgres=# WHEN (NEW.amount > 1000)
postgres=# EXECUTE FUNCTION log_order_change();

WHEN 在触发器层就过滤，比在函数体内判断更高效，也不会为不满足条件的行为调用函数。

语句级触发器

前面都是 FOR EACH ROW。如果想”一条语句只记一次”，用 FOR EACH STATEMENT：

postgres=# CREATE TRIGGER trg_audit_orders
postgres=# AFTER INSERT OR UPDATE OR DELETE ON orders
postgres=# FOR EACH STATEMENT EXECUTE FUNCTION audit_orders_stmt();  -- audit_orders_stmt() 为配套触发器函数，可按需定义

语句级触发器没有 NEW / OLD（因为是整批不是单行），适合做”这条语句执行了”的总览记录。

视图上的 INSTEAD OF 触发器

不可更新的视图（带 JOIN / 聚合）默认不能 INSERT。想让它可写，可以建 INSTEAD OF 触发器，把对视图的写操作”翻译”成对底层表的写：

postgres=# CREATE OR REPLACE FUNCTION upd_via_view()
postgres=# RETURNS trigger
postgres=# LANGUAGE plpgsql
postgres=# AS $$
postgres=# BEGIN
postgres=#   IF TG_OP = 'INSERT' THEN
postgres=#     INSERT INTO users(username, age) VALUES (NEW.username, NEW.age);
postgres=#     RETURN NEW;
postgres=#   END IF;
postgres=#   RETURN NULL;
postgres=# END;
postgres=# $$;

postgres=# CREATE TRIGGER trg_v INSTEAD OF INSERT ON adult_users  -- 前提：adult_users 是建立在 users 之上的视图（可按需自建）
postgres=# FOR EACH ROW EXECUTE FUNCTION upd_via_view();

Note

INSTEAD OF 只支持 FOR EACH ROW，且只能建在视图上，不能建在普通表上。它让视图”假装”可写，实际落到了底表。

存储过程（PROCEDURE）

存储过程（PROCEDURE）和函数长得很像，但关键区别：过程不返回值，主要用来”做事”，而且能自己管理事务（COMMIT / ROLLBACK）。

postgres=# CREATE OR REPLACE PROCEDURE transfer(
postgres=#   sender INT, receiver INT, amount NUMERIC)
postgres=# LANGUAGE plpgsql
postgres=# AS $$
postgres=# BEGIN
postgres=#   UPDATE accounts SET balance = balance - amount WHERE id = sender;
postgres=#   UPDATE accounts SET balance = balance + amount WHERE id = receiver;
postgres=# END;
postgres=# $$;

调用用 CALL，不是 SELECT：

postgres=# CALL transfer(1, 2, 100);

Note

函数用 SELECT 调，过程用 CALL 调。函数能在 SQL 表达式里用，过程不能出现在 SELECT 里。

过程里管理事务

过程最大的优势是能在内部 COMMIT，把一长段操作拆成多个可提交的小事务：

postgres=# CREATE OR REPLACE PROCEDURE batch_transfer()
postgres=# LANGUAGE plpgsql
postgres=# AS $$
postgres=# BEGIN
postgres=#   UPDATE accounts SET balance = balance - 100 WHERE id = 1;
postgres=#   UPDATE accounts SET balance = balance + 100 WHERE id = 2;
postgres=#   COMMIT;
postgres=#   UPDATE accounts SET balance = balance - 50 WHERE id = 2;
postgres=#   UPDATE accounts SET balance = balance + 50 WHERE id = 3;
postgres=#   COMMIT;
postgres=# EXCEPTION
postgres=#   WHEN others THEN
postgres=#     ROLLBACK;
postgres=#     RAISE;
postgres=# END;
postgres=# $$;

Warning

带内部 COMMIT 的过程，不能在已有事务块（BEGIN … COMMIT）里调用，否则会报错。它得在”自动提交”模式下直接 CALL。函数和触发器都不允许内部 COMMIT，只有过程可以。

函数 vs 过程

对比	函数 FUNCTION	过程 PROCEDURE
返回值	必须有	无
调用方式	SELECT	CALL
事务控制	不能 COMMIT	可以
能否在 SELECT 里用	能	不能
用途	计算、取数	执行业务操作

多个触发器的执行顺序

同一张表上可以挂多个同类触发器（比如两个 BEFORE INSERT）。它们的触发顺序默认按”触发器名字的字母序”排，不是建表顺序。想调整顺序，可以 ALTER TRIGGER ... RENAME 改名字，或删了重建。

Tip

触发器顺序有依赖时（比如 A 改了 NEW，B 再读），最好用清晰的命名约定，比如 trg_01_xxx、trg_02_yyy，一眼能看出先后。

临时关闭触发器

维护数据、批量导入时，可能想暂时关掉某张表的触发器（比如外键校验、审计日志），用 DISABLE TRIGGER：

postgres=# ALTER TABLE orders DISABLE TRIGGER trg_after_order_insert;
postgres=# -- 批量操作...
postgres=# ALTER TABLE orders ENABLE TRIGGER trg_after_order_insert;

Warning

关触发器能提速，但也跳过了校验和日志，数据可能不一致。操作完一定记得 ENABLE 回来，且仅限维护时段使用。

触发器里的特殊变量汇总

除了 NEW / OLD，还常用这几个判断上下文：

TG_OP：当前事件，‘INSERT’ / ‘UPDATE’ / ‘DELETE’ / ‘TRUNCATE’。
TG_WHEN：‘BEFORE’ / ‘AFTER’ / ‘INSTEAD OF’。
TG_LEVEL：‘ROW’ / ‘STATEMENT’。
TG_TABLE_NAME / TG_TABLE_SCHEMA：表名和模式名。

用它们能写一个”通吃”的审计触发器，根据 TG_OP 决定记什么：

postgres=# IF TG_OP = 'DELETE' THEN
postgres=#   INSERT INTO audit(log) VALUES ('删除了 ' || OLD.id);
postgres=#   RETURN OLD;
postgres=# END IF;

过程适用场景再谈

过程适合”一串要依次提交的业务动作”，比如批量转账、数据迁移、定时清理。它能内部 COMMIT，把一个大任务拆成多个可恢复的小事务。如果只是算个数、取条数据，用函数就够了，别硬写过程。

触发器不是银弹

触发器很强大，但也最容易被人滥用。把本该在应用层做的校验、日志全塞进触发器，结果写入变慢、出了错还难定位。我的原则：触发器只做”和这行数据强绑定、且不该被业务代码遗漏”的事，比如审计日志、派生字段规范化。可要可不要的逻辑，留在应用层。

过程与函数怎么选（再强调）

记不住就记这句：要”算结果”用函数（SELECT），要”做一串动作且可能中途提交”用过程（CALL）。转账、批量迁移、定时清理这种，过程最合适；取个数、格式化一下，函数就够。别因为过程能写 COMMIT 就什么都往过程里塞。

常见误区

触发器里写复杂逻辑会拖慢写入，且不易调试。能放在应用层做的校验，未必非要塞进触发器。
把函数用 CALL 调，或把过程用 SELECT 调，都会报错。
在事务块里 CALL 一个带 COMMIT 的过程，报错。
BEFORE 触发器忘记 RETURN NEW，导致插入的行被丢弃。
AFTER 触发器去改 NEW 没用，数据已经写完了，要在 BEFORE 里改。