Menu

PostgreSQL 触发器:CREATE TRIGGER 用法与示例

触发器在插入、更新或删除行时自动运行一个函数。在 PostgreSQL 中,先写一个返回 trigger 的触发器函数,再用 CREATE TRIGGER 把它挂到表上。

本页包含可运行的编辑器。编辑、运行,立即查看输出。

触发器让 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 的语句级触发器。

Coddy 编程语言插图

和 Coddy 一起学编程

开始