如何在PostgreSQL中实现事务结束时仅触发一次的表修改触发器？

要实现仅在事务结束且表x被修改时触发一次的触发器（用于记录最后修改时间戳），PostgreSQL没有直接的事务触发器，但可以通过临时表+语句级触发器的组合来模拟，具体步骤如下：

1. 创建存储最后修改时间的记录表 #

首先需要一张表来保存各表的最后修改时间：

CREATE TABLE IF NOT EXISTS table_last_modified (
    table_name text PRIMARY KEY,
    last_modified timestamptz NOT NULL DEFAULT CURRENT_TIMESTAMP
);

2. 编写触发器函数 #

这个函数的核心是用事务级临时表标记是否已经处理过当前表的修改，确保事务内只触发一次更新：

CREATE OR REPLACE FUNCTION mark_table_modified()
RETURNS TRIGGER AS $$
BEGIN
    -- 检查当前事务是否已经标记过该表的修改
    IF NOT EXISTS (SELECT 1 FROM pg_temp.table_modified_markers WHERE table_name = TG_TABLE_NAME) THEN
        -- 创建临时表，仅当前事务可见，事务提交时执行时间戳更新
        CREATE TEMP TABLE IF NOT EXISTS table_modified_markers (
            table_name text PRIMARY KEY
        ) ON COMMIT DO
        UPDATE table_last_modified
        SET last_modified = CURRENT_TIMESTAMP
        WHERE table_name = TG_TABLE_NAME;

        -- 若表未在记录中，插入初始记录
        INSERT INTO table_last_modified (table_name)
        VALUES (TG_TABLE_NAME)
        ON CONFLICT (table_name) DO NOTHING;

        -- 将当前表加入事务内的修改标记列表
        INSERT INTO pg_temp.table_modified_markers (table_name)
        VALUES (TG_TABLE_NAME);
    END IF;
    RETURN NULL; -- 语句级触发器无需返回行数据
END;
$$ LANGUAGE plpgsql VOLATILE;

3. 为表x创建语句级触发器 #

给目标表x绑定触发器，监听所有写操作（INSERT/UPDATE/DELETE）：

CREATE TRIGGER trigger_table_x_modified
AFTER INSERT OR UPDATE OR DELETE ON x
FOR EACH STATEMENT
EXECUTE FUNCTION mark_table_modified();

方案原理说明 #

• 临时表标记：pg_temp.table_modified_markers仅在当前事务内存在，用于避免同一事务内多次修改表x时重复触发更新逻辑。
• 事务提交时执行：ON COMMIT DO子句会在事务成功提交时才执行时间戳更新，确保只有事务结束且表被修改时才更新一次。
• 回滚不生效：如果事务回滚，临时表会被自动销毁，ON COMMIT DO的更新不会执行，符合业务预期。

这个方案可以扩展到多个表，只需为每个需要记录修改时间的表创建对应的语句级触发器即可。

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