PostgreSQL中如何在关联表更新时触发更新另一表的时间戳列

解决方案 #

针对你的需求，可以通过带字段变化判断的触发器函数实现目标：既在test_2的字段实际更新时同步刷新test表的update_ts，又能避免旧导入机制全表更新时的误操作。

步骤1：创建触发器函数 #

这个函数会判断test_2的s字段是否真的发生了变化，只有当值不同时才去更新test表的时间戳：

CREATE OR REPLACE FUNCTION update_test_timestamp()
RETURNS TRIGGER AS $$
BEGIN
    -- 仅当s字段的新旧值存在差异时，执行时间戳更新
    IF OLD.s IS DISTINCT FROM NEW.s THEN
        UPDATE test
        SET update_ts = NOW()
        WHERE i = NEW.i;
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

步骤2：绑定触发器到test_2表 #

给test_2添加行级AFTER UPDATE触发器，调用上面的函数：

CREATE TRIGGER trigger_update_test_ts
AFTER UPDATE ON test_2
FOR EACH ROW
EXECUTE FUNCTION update_test_timestamp();

效果说明 #

• 当你执行update test_2 set s = 'foo' where i = 2;这类实际修改s字段的操作时，触发器会自动更新test表中i=2的update_ts为当前时间。
• 若旧导入机制执行全表UPDATE但s字段值未发生变化，触发器函数里的判断会跳过更新操作，不会误修改test表中其他未关联变更的记录（比如i=1的行）。

执行上述操作后，再次查询test_view就能看到对应行的update_ts已被刷新。

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