触发器让 PostgreSQL 在表发生变化时自动调用一个函数。先写触发器函数,声明为 RETURNS trigger,再把它挂上去:CREATE TRIGGER name BEFORE UPDATE ON table FOR EACH ROW EXECUTE FUNCTION fn();。最经典的用法是让 updated_at 列保持最新:
只有文章 1 被更新了,所以只有它有时间戳。NEW 是即将写入的行;BEFORE 触发器可以修改它,并且必须把它返回。同一个函数可以挂到每一张有 updated_at 列的表上。
BEFORE、AFTER 和 INSTEAD OF
| 时机 | 何时运行 | 典型用途 |
|---|---|---|
BEFORE | 行写入之前 | 修改或校验 NEW,或者跳过这一行 |
AFTER | 行写入之后 | 写入其他表:审计日志、计数器 |
INSTEAD OF | 代替这次修改,只能用于视图 | 让视图可写 |
FOR EACH ROW 对每个受影响的行运行一次函数。FOR EACH STATEMENT(默认)对每条语句运行一次,即使没有任何行发生变化。在行级触发器中,NEW 保存新行(INSERT、UPDATE),OLD 保存原来的行(UPDATE、DELETE)。TG_OP 是触发它的操作名,TG_TABLE_NAME 是表名。
BEFORE 行级触发器的返回值决定接下来会怎样:返回 NEW(无论是否修改过)就继续,返回 NULL 就跳过这一行而不报错。AFTER 触发器的返回值会被忽略,所以按惯例返回 NULL。
用 AFTER 触发器写审计日志
审计表记录了两次 INSERT、一次从 10 到 12 的 UPDATE,以及一次对价格为 20 的商品的 DELETE。插入时 OLD 是 NULL,删除时 NEW 是 NULL,所以要用 coalesce。要保存整行,就把 to_jsonb(OLD) 和 to_jsonb(NEW) 存进一个 jsonb 列。
阻止或跳过修改
BEFORE 触发器可以用 RAISE EXCEPTION 拒绝修改,这会中止整条语句;也可以返回 NULL 跳过某一行:
DELETE 删掉了草稿,悄悄保留了已付款的发票。BEFORE DELETE 触发器返回 OLD,表示允许删除继续。如果想让整条语句失败,就把 RETURN NULL 换成 RAISE EXCEPTION 'invoice % is paid', OLD.id;。比起一行悄悄留下来,报错更容易排查。
只在某一列变化时触发
UPDATE OF column 把触发器限制在设置了这一列的更新上。WHEN 条件为假时,不会调用函数。IS DISTINCT FROM 把 NULL 当作一个值,所以变成 NULL 或从 NULL 变成其他值也能捕获到:
第一次更新设置的是相同的价格,所以 WHEN 为假,计数器保持 0。第二次修改了价格,price_changed 最终为 1。
查看、禁用和删除触发器
-- triggers you created (excludes the ones behind foreign keys)
SELECT tgrelid::regclass AS table_name, tgname
FROM pg_trigger WHERE NOT tgisinternal;
ALTER TABLE products DISABLE TRIGGER products_price;
ALTER TABLE products ENABLE TRIGGER products_price;
DROP TRIGGER products_price ON products;
在 psql 中,\d products 会列出表上的触发器。CREATE OR REPLACE TRIGGER 从 PostgreSQL 14 开始支持,EXECUTE FUNCTION 从 PostgreSQL 11 开始支持;较早的代码写的是 EXECUTE PROCEDURE,现在仍然可以用。写入自身所在表的触发器可能会再次触发自己,所以要防止循环(pg_trigger_depth() 返回嵌套层数)。触发器函数章节列出了所有 TG_ 变量,PL/pgSQL 本身见函数和存储过程。
常见问题
PostgreSQL 触发器可以不用函数吗?
不行。CREATE TRIGGER 总是调用一个声明为 RETURNS trigger 的函数。先写好函数(通常用 PL/pgSQL),再把它挂上去。
同一张表上的触发器按什么顺序执行?
同一事件上同一类的触发器按名字的字母顺序执行。如果顺序很重要,就给名字加上数字或字母前缀。
触发器出错会回滚语句吗?
会。在触发器中抛出的错误(比如用 RAISE EXCEPTION)会中止触发它的语句,除非你用保存点捕获它,否则整个事务都会中止。
TRUNCATE 会触发触发器吗?
行级 DELETE 触发器不会被触发。要响应它,就创建一个 BEFORE TRUNCATE 或 AFTER TRUNCATE 的语句级触发器。