Mariam Mariam - 1 month ago 8
MySQL Question

Trigger Syntax Error in MySQL on IF/THEN (ERROR 1064)

I have an error while executing this query:

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.

Comments