MYSQL 触发器 实践案例

场景：

利用触发器实现更新楼层表的楼层名称时，自动更新与楼层相关的AP位置信息的楼层名称；
删除楼层时自动删除与楼层相关的AP位置信息。

# 表1：
CREATE TABLE `tb_ap_loc` (
  `id` int NOT NULL AUTO_INCREMENT,
  `floor` varchar(255) COLLATE utf8_bin DEFAULT NULL,
  `ap` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
  `left` int DEFAULT '0',
  `top` int DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `fk_floor` (`floor`),
  KEY `fk_ap` (`ap`),
  # 这里两条外键约束可以不做，如果做了外键约束，其实也可以实现自动更新与删除的功能
  CONSTRAINT `fk_ap` FOREIGN KEY (`ap`) REFERENCES `tb_ap` (`ap_name`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_floor` FOREIGN KEY (`floor`) REFERENCES `tb_floor` (`floor`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=131 DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
 
# 表2：
CREATE TABLE `tb_floor` (
  `id` int NOT NULL AUTO_INCREMENT,
  `floor` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  `pid` int DEFAULT NULL,
  `map` varchar(255) COLLATE utf8_bin DEFAULT NULL,
  `ord` int DEFAULT NULL,
  PRIMARY KEY (`id`,`floor`) USING BTREE,
  KEY `floor` (`floor`)
) ENGINE=InnoDB AUTO_INCREMENT=58 DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
 
# 表2触发器：
 
CREATE TRIGGER `tri_floor_delete` AFTER DELETE ON `tb_floor` FOR EACH ROW
delete from tb_ap_loc where floor = OLD.floor;
 
CREATE TRIGGER `tri_floor_update` AFTER UPDATE ON `tb_floor` FOR EACH ROW
update tb_ap_loc set floor=NEW.floor where floor=OLD.floor;

效果：

当更新tb_floor里记录的floor字段时，与之相关的tb_ap_loc的floor字段也会同步更新。

当删除tb_floor里记录时，与之相关的tb_ap_loc的floor也会自动删除。