MySQL 触发器删除整个表而不是一个条目

Question

我有两个表 t1 (alternativeRoute) & t2 (alternativeRoute_addressInformation)。

挑战在于，如果在 T2 中删除所有具有相同 ID 的条目(全部删除查询或连续删除)，则 T1 中的条目也应删除。

为此，我创建了一个 SQL 查询，如果我在从 t2 中删除具有相同 ID 的条目后手动运行它，它会完美运行。

DELETE FROM traffic.traffic_alternativeRoute WHERE altRoute_id NOT IN (
SELECT distinct traffic.traffic_alternativeRoute_addressInformation.altRoute_id
FROM traffic.traffic_alternativeRoute_addressInformation);

但我想自动执行该操作，因此我决定创建一个触发器:

DELIMITER $$
CREATE TRIGGER clear_traffic_alternativeRoute
AFTER DELETE ON traffic.traffic_alternativeRoute_addressInformation
FOR EACH ROW
BEGIN
DELETE FROM traffic.traffic_alternativeRoute WHERE altRoute_id NOT IN (
SELECT distinct    'traffic.traffic_alternativeRoute_addressInformation.altRoute_id'
FROM traffic.traffic_alternativeRoute_addressInformation);
END; $$
DELIMITER ;

现在的问题是:触发触发器，但不是只删除 t1 中的条目，而是删除两个表中的所有条目。

有谁知道为什么这段代码在使用触发器时不起作用？

测试数据:

T1:

INSERT INTO `traffic_alternativeRoute` (`info_id`, `altRoute_id`, `altRoute_desc`) VALUES
(66, 63, '');
INSERT INTO `traffic_alternativeRoute` (`info_id`, `altRoute_id`, `altRoute_desc`) VALUES
(66, 64, '');

T2:

INSERT INTO `traffic_alternativeRoute_addressInformation` (`altRoute_id`, altRouteAddress_id`, `altRoute_address`, `altRoute_address_houseNumber`, `altRoute_locality`, `altRoute_postal_code`, `altRoute_order_number`, `altRoute_pos_lat`, `altRoute_pos_lng`) VALUES
(63, 249, 'Kölner Straße', 445, 'Kürten', '51515', 1, 51.03150342683682, 7.209327220916748),
(63, 250, 'Kölner Straße', 488, 'Kürten', '51515', 2, 51.02742788461119, 7.205550670623779),
(64, 251, 'Engelsgasse', 14, 'Kürten', '51515', 3, 51.02900686236984, 7.199242115020752),
(64, 252, 'Cliev', 5, 'Kürten', '51515', 4, 51.03193525137229, 7.20728874206543);

预期结果:

删除ID 64的条目后，T1应该只包含ID 63的条目。

编辑:

有一件事我没有说，也没有再记在心上，那就是我在 T1 上有一个 ON CASCADE。这可能是问题的一部分吗？

CREATE TABLE IF NOT EXISTS `traffic`.`traffic_alternativeRoute` (
  `info_id` INT NULL COMMENT '',
  `altRoute_id` INT NOT NULL AUTO_INCREMENT COMMENT '',
  `altRoute_desc` VARCHAR(256) NULL COMMENT '',
  PRIMARY KEY (`altRoute_id`)  COMMENT '',
  INDEX `fk_info_id_idx` (`info_id` ASC)  COMMENT '',
  CONSTRAINT `fk_info_id`
   FOREIGN KEY (`info_id`)
   REFERENCES `traffic`.`traffic_info` (`info_id`)
   ON DELETE CASCADE
   ON UPDATE NO ACTION)
 ENGINE = InnoDB
 DEFAULT CHARACTER SET = utf8
 COLLATE = utf8_general_ci;

CREATE TABLE IF NOT EXISTS   `traffic`.`traffic_alternativeRoute_addressInformation` (
  `altRoute_id` INT NULL COMMENT '',
  `altRouteAddress_id` INT NOT NULL AUTO_INCREMENT COMMENT '',
  `altRoute_address` VARCHAR(128) NOT NULL COMMENT '',
  `altRoute_address_houseNumber` INT NOT NULL COMMENT '',
  `altRoute_locality` VARCHAR(45) NOT NULL COMMENT '',
  `altRoute_postal_code` VARCHAR(10) NOT NULL COMMENT '',
  `altRoute_order_number` INT NOT NULL COMMENT '',
  `altRoute_pos_lat` DOUBLE NOT NULL COMMENT '',
  `altRoute_pos_lng` DOUBLE NOT NULL COMMENT '',
  PRIMARY KEY (`altRouteAddress_id`)  COMMENT '',
  INDEX `fk_altRoute_id_idx` (`altRoute_id` ASC)  COMMENT '',
  CONSTRAINT `fk_altRoute_id`
    FOREIGN KEY (`altRoute_id`)
    REFERENCES `traffic`.`traffic_alternativeRoute` (`altRoute_id`)
    ON DELETE CASCADE
ON UPDATE NO ACTION)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_general_ci;

这里是 entrie DB - ERD，希望对您有所帮助:

Database Design

Answer 1

为什么要使用子查询？您可以使用触发器完成所需的操作，如下所示:

DELIMITER //

CREATE TRIGGER `clear_traffic_alternativeRoute`
  AFTER DELETE ON `traffic_alternativeRoute_addressInformation`
FOR EACH ROW
BEGIN
  /*
  DELETE FROM traffic.traffic_alternativeRoute WHERE altRoute_id NOT IN (
  SELECT distinct 'traffic.traffic_alternativeRoute_addressInformation.altRoute_id'
  FROM traffic.traffic_alternativeRoute_addressInformation);
  */
  DELETE FROM `traffic_alternativeRoute`
  WHERE `altRoute_id` NOT IN (OLD.`altRoute_id`);
END//

DELIMITER ;

SQL Fiddle demo