mysql语法问题

发布于 2024-09-06 08:54:12 字数 711 浏览 9 评论 0原文

我正在尝试在 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`);

提前致谢！

原文

I´m trying to create a trigger on MySQL but I´m having a syntax problem, which I was not able to find. If someone more experience could help me it would be great (it´s the first time I use MySQL!)...

The reason why I´m creating this trigger is for deleting all the orphan "labels", which has a many-to-many relation with "service_descriptor" (this two entities are linked by service_labels).

The code I have is:

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`);

Thanks in advance !

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

丿*梦醉红颜 2024-09-13 08:54:12

您已经错过并

尝试此代码

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`                         
                    );

You have missed AND

Try this code

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`                         
                    );

回复收藏 0 原文

慕巷 2024-09-13 08:54:12

您的触发器存在一些问题，最明显的是“FOR EACH ROW”的位置，以及您将 OLD 视为表格的方式，而实际上它只是一行。

这应该适合你：

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`                        
    );

There are a couple of problems with your trigger, most notably the placement of "FOR EACH ROW", and the way you are treating OLD like a table, when it's really just a row.

This should work for you:

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`                        
    );

回复收藏 0 原文

坐在坟头思考人生 2024-09-13 08:54:12

谢谢大家...由于您的帮助，我终于解决了这个问题...

最后的工作是：

在restdb上删除后创建触发器trg_delete_orphan_label。服务描述符
对于每一行
从restdb删除。标签
其中 id 不在 (
选择restdb.service_labels.id_label
FROM restdb.service_labels ）；