如何通过触发器实现其他表属性变更时自动更新关联表指定字段

以下方案完全匹配你的两个业务需求，可直接部署使用。

所需触发器分类 #

• 表A更新后触发器：监听a_active字段变更，同步更新所有关联的A_B记录
• 表B更新后触发器：监听b_active字段变更，同步更新所有关联的A_B记录
• A_B表插入/更新前触发器：校验手动修改active字段的合法性

具体代码实现 #

1. 表A的更新触发器 #

DELIMITER //
CREATE TRIGGER trg_a_after_update_active
AFTER UPDATE ON `表A`
FOR EACH ROW
BEGIN
  -- 只有a_active字段发生变更才执行同步
  IF OLD.a_active != NEW.a_active THEN
    UPDATE `A_B` 
    SET active = NEW.a_active AND (SELECT b_active FROM `表B` WHERE id = `A_B`.b_id_fk)
    WHERE a_id_fk = NEW.id;
  END IF;
END //
DELIMITER ;

2. 表B的更新触发器 #

DELIMITER //
CREATE TRIGGER trg_b_after_update_active
AFTER UPDATE ON `表B`
FOR EACH ROW
BEGIN
  -- 只有b_active字段发生变更才执行同步
  IF OLD.b_active != NEW.b_active THEN
    UPDATE `A_B` 
    SET active = NEW.b_active AND (SELECT a_active FROM `表A` WHERE id = `A_B`.a_id_fk)
    WHERE b_id_fk = NEW.id;
  END IF;
END //
DELIMITER ;

3. A_B表的修改校验触发器 #

DELIMITER //
-- 插入新关联关系时的校验
CREATE TRIGGER trg_ab_before_insert
BEFORE INSERT ON `A_B`
FOR EACH ROW
BEGIN
  DECLARE valid_a BOOLEAN;
  DECLARE valid_b BOOLEAN;
  -- 仅当要把active设为true时才做校验，设为false直接放行
  IF NEW.active = TRUE THEN
    SELECT a_active INTO valid_a FROM `表A` WHERE id = NEW.a_id_fk;
    SELECT b_active INTO valid_b FROM `表B` WHERE id = NEW.b_id_fk;
    IF NOT (valid_a AND valid_b) THEN
      SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '关联关系启用失败：对应A或B记录已处于禁用状态';
    END IF;
  END IF;
END //

-- 更新关联关系时的校验
CREATE TRIGGER trg_ab_before_update
BEFORE UPDATE ON `A_B`
FOR EACH ROW
BEGIN
  DECLARE valid_a BOOLEAN;
  DECLARE valid_b BOOLEAN;
  -- 仅当主动把active从false改成true时才做校验
  IF NEW.active = TRUE AND OLD.active != NEW.active THEN
    SELECT a_active INTO valid_a FROM `表A` WHERE id = NEW.a_id_fk;
    SELECT b_active INTO valid_b FROM `表B` WHERE id = NEW.b_id_fk;
    IF NOT (valid_a AND valid_b) THEN
      SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '关联关系启用失败：对应A或B记录已处于禁用状态';
    END IF;
  END IF;
END //
DELIMITER ;

注意事项 #

• 以上代码适配MySQL 8.0及以上版本，PostgreSQL等其他数据库只需调整触发语法、异常抛出逻辑即可复用核心逻辑
• 现有外键约束已经保证a_id_fk、b_id_fk必然存在对应记录，无需额外做存在性校验
• 如果A/B记录删除，外键默认会阻止删除或级联删除关联表记录，无需触发器额外处理

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