实现route_details更新后满足条件时自动更新route表的触发器问询

我有两张数据表：route与route_details。其中route_details表包含shipment_id、route_id以及状态字段active（取值为1或0），且每个shipment_id仅关联唯一的route_id。需求为：当route_details表中某条记录的active被设置为0时，创建触发器，仅当该route_id对应的所有route_details记录的active均为0时，将route表中对应记录的active设为0。我尝试使用嵌套子查询、case语句及if语句，但逻辑仍未梳理清晰，目前编写的代码如下：

CREATE TRIGGER inactive_route
AFTER UPDATE
ON route_details FOR EACH ROW
select if(active is null,route_id,0)  from route_details where route_id in (
select route_id from route_details  where shipment_id=107
) and active = OLD.active

核心逻辑梳理 #

触发器需要满足两个关键条件：

• 仅在route_details的记录从active=1改为active=0时触发检查
• 确认该route_id下所有关联的route_details记录active都为0后，才更新route表的对应状态

正确实现代码 #

DELIMITER //
CREATE TRIGGER inactive_route
AFTER UPDATE ON route_details
FOR EACH ROW
BEGIN
    -- 仅处理从激活改为禁用的记录
    IF OLD.active = 1 AND NEW.active = 0 THEN
        -- 检查当前route_id下是否还有激活的明细记录
        IF NOT EXISTS (
            SELECT 1 
            FROM route_details 
            WHERE route_id = NEW.route_id 
              AND active = 1
        ) THEN
            -- 所有明细都已禁用，同步更新route表状态
            UPDATE route 
            SET active = 0 
            WHERE route_id = NEW.route_id;
        END IF;
    END IF;
END //
DELIMITER ;

代码关键点说明 #

• 分隔符修改：由于触发器包含多条SQL语句，临时将分隔符改为//，避免与默认的;冲突，执行完触发器创建后再改回原分隔符。
• 触发时机控制：通过OLD.active = 1 AND NEW.active = 0过滤掉不必要的触发场景（比如从0改0、从1改1），提升效率。
• 高效状态检查：使用NOT EXISTS子查询快速判断是否存在激活的明细记录，相比统计所有记录的方式性能更优。
• 动态关联路由ID：使用NEW.route_id获取当前修改记录对应的路由ID，替代原代码中硬编码的shipment_id=107，保证逻辑通用性。

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