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 !
Thanks everyone ... I´ve finaly solved it because of your help...
In the end the work is:
CREATE TRIGGER trg_delete_orphan_label
AFTER DELETE ON restdb
.service_descriptor
FOR EACH ROW
DELETE FROM restdb
.labels
WHERE id
NOT IN (
SELECT restdb
.service_labels
.id_label
FROM restdb
.service_labels
);