如何创建触发器实现ROOM表更新时同步维护ROOM_HISTORY表？

以MySQL为例，你可以创建一个AFTER UPDATE触发器来完成需求，具体逻辑分为两步：更新旧房间的历史记录结束时间，插入新房间的历史记录。

1. 触发器逻辑说明 #

• 触发时机：当ROOM表中某条记录的ROOM_ID发生变更（OLD.ROOM_ID != NEW.ROOM_ID）时执行
• 第一步：更新ROOM_HISTORY中，对应该CLASS_ID且关联旧ROOM_ID的有效历史记录，将其END_DATE设为当前时间（标记该记录失效）
• 第二步：向ROOM_HISTORY插入新记录，记录新ROOM_ID与CLASS_ID的关联关系，START_DATE设为当前时间，END_DATE留空（表示当前有效）

2. 具体SQL代码 #

DELIMITER //

CREATE TRIGGER trg_room_update_history
AFTER UPDATE ON ROOM
FOR EACH ROW
BEGIN
    -- 仅当ROOM_ID发生变更时执行操作
    IF OLD.ROOM_ID != NEW.ROOM_ID THEN
        -- 更新旧ROOM_ID对应的历史记录，设置结束时间为当前时间
        UPDATE ROOM_HISTORY
        SET END_DATE = NOW()
        WHERE CLASS_ID = NEW.CLASS_ID
          AND ROOM_ID = OLD.ROOM_ID
          AND END_DATE IS NULL; -- 只更新未结束的有效记录

        -- 插入新的ROOM_ID关联历史记录
        INSERT INTO ROOM_HISTORY (ROOM_ID, CLASS_ID, START_DATE, END_DATE)
        VALUES (NEW.ROOM_ID, NEW.CLASS_ID, NOW(), NULL);
    END IF;
END //

DELIMITER ;

3. 注意事项 #

• 如果使用其他数据库（如Oracle、SQL Server），语法会略有差异：
  • Oracle需使用:OLD、:NEW替代OLD、NEW，且触发器结构不同
  • SQL Server需使用UPDATE()函数判断字段是否变更，语法格式也有区别
• 确保ROOM_HISTORY表的CLASS_ID和ROOM_ID有合适的索引，避免批量更新时性能问题
• 若需要保证时间的一致性，可以用同一个变量存储当前时间，比如SET @current_time = NOW();，再在更新和插入中使用该变量

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