PostgreSQL语句级触发器报错：含列列表无法指定过渡表，如何获取更新行集？

PostgreSQL 不允许在带列列表（OF value）的语句级触发器中声明过渡表，这就是你最初创建触发器时报错的核心原因。要实现仅在value字段修改时批量更新modified时间戳、同时避免递归触发的需求，可采用以下方案：

实现步骤 #

1. 创建触发器函数 #

CREATE OR REPLACE FUNCTION my_schema.my_trigger_function()
RETURNS TRIGGER
LANGUAGE PLPGSQL
AS $$
BEGIN
    -- 跳过递归触发：当触发器因更新modified字段被调用时，触发深度大于1，直接返回
    IF pg_trigger_depth() > 1 THEN
        RETURN NULL;
    END IF;

    -- 批量更新仅value字段发生变化的行的modified时间戳
    UPDATE my_schema.my_table t
    SET modified = NOW()
    FROM NEW_TABLE nt
    JOIN OLD_TABLE ot ON nt.id = ot.id
    WHERE t.id = nt.id
      -- 精准过滤value字段确实被修改的行，等价于AFTER UPDATE OF value的逻辑
      AND nt.value IS DISTINCT FROM ot.value;

    RETURN NULL;
END;
$$;

2. 创建语句级触发器 #

CREATE TRIGGER my_trigger
AFTER UPDATE
ON my_schema.my_table
REFERENCING NEW TABLE AS NEW_TABLE OLD TABLE AS OLD_TABLE
FOR EACH STATEMENT
EXECUTE PROCEDURE my_schema.my_trigger_function();

方案细节说明 #

• 避免递归触发：pg_trigger_depth()函数返回当前触发器的嵌套调用深度，当我们更新modified字段时会再次触发触发器，但此时深度为2，函数直接返回NULL，不会执行重复的更新逻辑。
• 等价替代AFTER UPDATE OF value：通过NEW_TABLE（更新后的行集）和OLD_TABLE（更新前的行集）关联，用nt.value IS DISTINCT FROM ot.value过滤出value字段实际被修改的行，完全达到仅在value变更时触发更新的效果。
• 批量处理性能：整个更新逻辑是单次SQL批量操作，相比行级触发器逐行处理的方式，在数千行批量更新场景下性能提升显著。

为什么不坚持使用AFTER UPDATE OF value #

PostgreSQL的语法限制明确：带列列表的语句级触发器无法使用过渡表，而语句级触发器本身没有行级的NEW/OLD变量，若不用过渡表则无法获取本次更新的行集合。上述方案通过过渡表+条件过滤的方式，既绕开了语法限制，又完美满足你的业务需求。

内容的提问来源于stack exchange，提问作者Pavlo Kuchereshko