WITH (...) IF ... IN ... in MySQL trigger

Question

Given the simple table (I am using MySQL Server 8.0.17)

CREATE TABLE folders (
  id varchar(3) NOT NULL,
  parent varchar(3) DEFAULT NULL,
  PRIMARY KEY (id),
  KEY fk_folders_parent_idx (parent),
  CONSTRAINT fk_folders_parent FOREIGN KEY (parent) REFERENCES folders(id) ON DELETE RESTRICT ON UPDATE RESTRICT
);

I want to prevent circular references. I try this by creating an update trigger (since it is not possible to create a circular dependency when inserting items one by one).

The trigger code looks like this (I orientated myself here and here):

DELIMITER $$

CREATE TRIGGER `test`.`folders_BEFORE_UPDATE` BEFORE UPDATE ON `folders` FOR EACH ROW
BEGIN
    WITH RECURSIVE children (id) AS 
    (
        SELECT id FROM folders WHERE parent = NEW.id
        UNION ALL
        SELECT f.id FROM folders f INNER JOIN children ON f.parent = children.id
    ) 
    IF NEW.parent IN children THEN
        signal sqlstate '45000' set message_text = 'My Error Message'
    END IF
END$$

I get the very helpful error message:

Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IF NEW.parent IN children THEN         signal sqlstate '45000' set message_text ' at line 9

I believe it is because I cannot use WITH children (...) IF NEW.parent IN children

I tried WITH children (...) IF EXISTS (SELECT id FROM children WHERE id = NEW.parent), but that got the same response.

MySQL Workbench tells me, that the BEGIN in line 4 does not have an END. However I don't think that's the problem here.

Answer 1

My MySQL is getting a bit rusty and outdated since most my tasks are with MS SQL now, but try something like this:

DECLARE found INT;

WITH RECURSIVE children (id) AS 
(
   SELECT id FROM folders WHERE parent = NEW.id
   UNION ALL
   SELECT f.id FROM folders f INNER JOIN children ON f.parent = children.id
) 
SELECT id INTO found
FROM children 
WHERE id = NEW.parent
LIMIT 1
;

IF NEW.parent = found THEN
   signal sqlstate '45000' set message_text = 'My Error Message'
END IF

---

Edit (Le 'nton's final solution below):

DELIMITER $$

DROP TRIGGER IF EXISTS `test`.`folders_BEFORE_UPDATE`$$

CREATE TRIGGER `test`.`folders_BEFORE_UPDATE` BEFORE UPDATE ON `folders` FOR EACH ROW
BEGIN
    DECLARE result varchar(3);

    WITH RECURSIVE children (id) AS 
    (
        SELECT id FROM folders WHERE parent = NEW.id
        UNION ALL
        SELECT f.id FROM folders f INNER JOIN children ON f.parent = children.id
    ) SELECT id INTO result FROM children WHERE id = NEW.parent LIMIT 1;

    IF NEW.parent = result then
        signal sqlstate '45000' set message_text = 'Circular dependency detected!';
    END IF;
END$$