如何使用insert触发器更新同code旧行的exp_date时间戳？

结论 #

完全可以通过PostgreSQL的INSERT触发器实现该需求，核心采用AFTER INSERT触发器搭配自定义触发器函数实现，具体操作如下：

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

CREATE OR REPLACE FUNCTION update_old_record_exp_date()
RETURNS TRIGGER AS $$
BEGIN
  -- 找到同code下最新的旧行，更新其exp_date为新行eff_date的前一天
  UPDATE test
  SET exp_date = NEW.eff_date - INTERVAL '1 day'
  WHERE code = NEW.code
    AND id != NEW.id -- 排除刚插入的新行
    AND eff_date = (
      SELECT MAX(eff_date)
      FROM test
      WHERE code = NEW.code AND id != NEW.id
    );
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

函数逻辑说明：如果当前插入的code已有历史记录，就找到历史记录中生效日期最晚的那条，把它的失效日期更新为新记录生效日期的前一天；如果是第一次插入该code的记录，UPDATE语句不会匹配到数据，无额外操作，不影响新行插入。

步骤2：绑定INSERT触发器 #

CREATE TRIGGER trg_after_insert_test
AFTER INSERT ON test
FOR EACH ROW
EXECUTE FUNCTION update_old_record_exp_date();

这里选择AFTER INSERT而非BEFORE INSERT的原因是：你插入时没有指定eff_date，使用的是默认值now()，只有插入动作执行完成后，才能拿到NEW.eff_date的实际生成值。

效果验证 #

按照你的示例场景，表内已有code=12345的最新记录是id=5，exp_date为2025-12-31，执行插入语句：

INSERT INTO test (code) VALUES (12345);

触发逻辑后：

• 新插入的行eff_date为插入时的系统时间（假设为2024-06-01），exp_date默认值为2025-12-31
• 旧记录id=5的exp_date会被自动更新为2024-05-31，完全符合需求要求。

注意事项 #

• 单条插入场景下该逻辑完全可用，如果需要支持批量插入同code的多条记录，可调整触发器逻辑，按照插入的eff_date排序依次更新旧记录即可
• 可给(code, eff_date)添加联合索引，提升UPDATE语句的查询效率
• 如果业务允许同code存在相同eff_date的记录，可调整UPDATE的匹配规则，优先取id最大的旧行更新，避免同时修改多条记录

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