mysql语法问题

Question

我正试图在MySQL上创建一个触发器，但我遇到了一个语法问题，我找不到它。如果有更多经验的人可以帮助我，那就太好了(这是我第一次使用MySQL！)

我之所以创建这个触发器是为了删除所有孤立的“标签”，它与"service_descriptor“有多对多的关系(这两个实体由service_labels链接)。

我的代码是：

CREATE TRIGGER `trg_delete_orphan_label` AFTER DELETE 
FOR EACH ROW ON `restdb`.`service_labels`  
  DELETE FROM `restdb`.`labels`
   WHERE EXISTS (SELECT *
                   FROM old D 
              LEFT_JOIN `restdb`.`service_labels` SL ON SL.`id_label` = D.`id_label` 
                                                    AND D.`id_service` = SL.`id_service`
                  WHERE SL.`id_label` IS NULL
                        `restdb`.`labels`.`id` = D.SL.`id_label`); 

提前感谢！

Answer 1

谢谢大家。多亏了你的帮助我终于解决了。

最后，我们的工作是：

在restdb上删除后创建触发器trg_delete_orphan_label。service_descriptor

对于每个行，DELETE FROM restdb.labels WHERE id NOT IN ( SELECT restdb FROM restdb.service_labels )；

Answer 2

你错过了和

试试这段代码

CREATE TRIGGER `trg_delete_orphan_label` AFTER DELETE FOR EACH ROW ON `restdb`.`service_labels`   
    DELETE 
    FROM 
        `restdb`.`labels` 
    WHERE 
        EXISTS (SELECT 
                    * 
                FROM 
                    old D LEFT_JOIN 
                    `restdb`.`service_labels` SL ON 
                        SL.`id_label` = D.`id_label` AND 
                        D.`id_service` = SL.`id_service` 
                WHERE 
                    SL.`id_label` IS NULL AND
                    `restdb`.`labels`.`id` = D.SL.`id_label`                         
                    );  

Answer 3

触发器有几个问题，最明显的是"FOR EACH ROW“的位置，以及当它实际上只是一行的时候，你像对待一张表一样对待旧的方式。

这对你来说应该是可行的：

DROP TRIGGER IF EXISTS `trg_delete_orphan_label`;

CREATE TRIGGER `trg_delete_orphan_label` AFTER DELETE ON `service_labels` 
FOR EACH ROW
    DELETE FROM `labels`
    WHERE `id` = OLD.`id_label`
    AND NOT EXISTS (
        SELECT NULL
        FROM `service_labels` SL 
        WHERE SL.`id_label` = `labels`.`id`                        
    );