求助：MySQL触发器仅更新特定ID状态的实现问题

现有表结构 #

commons_db.sites表 #

user_id | active 
1       |    0   
2       |    1   

commons_db.users表 #

site_id  | user_id | status
50       |    1    |   1
51       |    2    |   0

需求与问题 #

需求：创建触发器，当更新sites表的active列时，同步更新users表对应记录的status列。

当前问题：使用的更新语句会修改所有关联的user_id的status值，而非仅被更新的那条记录；尝试添加AND OLD.active != NEW.active到WHERE子句后无效果。

问题分析与解决方案 #

核心问题 #

1. 子查询未限定目标范围：你的更新语句中的子查询关联了所有sites和users的记录，导致所有匹配的user_id都被批量修改，而非仅当前被更新的那条sites记录对应的用户。
2. 条件放置错误：OLD.active != NEW.active是触发器中判断值是否变更的条件，应该放在触发器的逻辑判断里，而非更新语句的WHERE子句中。

正确触发器实现 #

DELIMITER //
CREATE TRIGGER sync_sites_active_to_users_status
AFTER UPDATE ON commons_db.sites
FOR EACH ROW
BEGIN
    -- 仅当active字段值确实发生变化时执行更新
    IF OLD.active != NEW.active THEN
        UPDATE commons_db.users
        SET `status` = NOT NEW.active
        WHERE user_id = NEW.user_id;
    END IF;
END //
DELIMITER ;

关键说明 #

• FOR EACH ROW：确保每条被更新的sites记录都会触发独立的同步逻辑，精准作用于单条记录。
• IF OLD.active != NEW.active：避免在active值未发生变化时执行无意义的更新操作，提升性能。
• 直接通过NEW.user_id定位：无需关联查询，直接锁定要更新的users表记录，避免批量修改错误。

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