PgSQL触发器需求：插入/更新time_info时自动同步temp_id

你的触发器代码存在几个关键问题导致未按预期生效：

• 使用了AFTER触发器：此时记录已经写入数据库，修改NEW变量不会改变最终存储的数据，必须用BEFORE触发器在写入前修改行内容
• 错误使用UPDATE NEW语法：NEW是触发器中的行变量，不是表，不能用UPDATE语句操作，直接赋值即可
• 触发条件太宽泛：所有UPDATE操作都会触发触发器，没必要，应该只在duration字段变化时触发

下面是修正后的完整解决方案：

1. 创建正确的触发器函数 #

CREATE OR REPLACE FUNCTION update_time_info_temp_id() 
RETURNS trigger AS $update_time_info_temp_id$
BEGIN
    -- 从category表匹配duration对应的temp_id，直接赋值给当前行的temp_id
    SELECT temp_id INTO NEW.temp_id
    FROM category
    WHERE duration = NEW.duration;

    -- 若没有匹配的duration，temp_id会被设为NULL，可根据业务需求调整默认值
    RETURN NEW;
END;
$update_time_info_temp_id$ LANGUAGE plpgsql;

2. 创建BEFORE触发器并优化触发条件 #

CREATE TRIGGER update_time_info_temp_id
BEFORE INSERT OR UPDATE OF duration ON time_info
FOR EACH ROW
EXECUTE FUNCTION update_time_info_temp_id();

代码说明 #

• BEFORE触发器：在记录插入或更新前执行函数，修改NEW变量后，数据库会直接用修改后的内容写入表
• UPDATE OF duration：仅当duration字段被更新时才触发触发器，避免无意义的执行，提升性能
• SELECT ... INTO NEW.temp_id：简洁地从category表查询匹配的temp_id，直接赋值给当前行的目标字段

测试你的场景：

• 插入duration=60的记录时，触发器会自动将temp_id设为234
• 更新该记录的duration为90时，触发器会重新查询并把temp_id更新为345

内容的提问来源于stack exchange，提问作者Ninja-aman