Mariam Mariam - 1 month ago 8
MySQL Question

IF THEN syntax error in My SQL (1064 error)

I have error while writing this qyery

CREATE TRIGGER insert_Topics
BEFORE INSERT
ON Topics
FOR EACH ROW
BEGIN
IF (SELECT COUNT(*) FROM Subjects WHERE ID=new.SubjectID)=0
THEN
INSERT error_msg VALUES ('Foreign Key Constraint Violated!');
END IF;
END;
delimiter ;


The error says

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 '' at line 8

and it points to the word THEN

Any help ? Thanks in advance

Answer

Both

CREATE TRIGGER insert_Topics
BEFORE INSERT
ON Topics
FOR EACH ROW
BEGIN
  IF NOT EXISTS(SELECT 1 FROM Subjects WHERE ID=NEW.SubjectID LIMIT 1) THEN
    INSERT INTO error_msg VALUES ('Foreign Key Constraint Violated!');
  END IF;
END;

and

CREATE TRIGGER insert_Topics
BEFORE INSERT
ON Topics
FOR EACH ROW
BEGIN
  IF (SELECT 1 FROM Subjects WHERE ID=NEW.SubjectID LIMIT 1) IS NULL THEN
    INSERT INTO error_msg VALUES ('Foreign Key Constraint Violated!');
  END IF;
END;

work for me, so your syntax problems probably lie elsewhere.