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

Question

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 1

I know this is an old question, but I thought I'd share my thoughts over what I think the error was, in case anyone sees this in the future (feel free to community-edit if i've made any mistakes!).

The reason there were issues in the original question seems to be with the use of DELIMITER, and what it does, as explained here: What does DELIMITER // do in a Trigger?

In the given code:

        INSERT error_msg VALUES ('Foreign Key Constraint Violated!');
END IF;
END;
delimiter ;

The resetting of delimiter with "delimiter ;" indicates that it changed previously to another delimiter, say:

DELIMITER //
--TRIGGER1
--TRIGGER2
--TRIGGERn
delimiter ; -- Aggravates me that this wasn't capitalised :P

The reason for the delimiter to be set to "//" (or others), is to allow multiple statements to be delimited, and what I'm thinking, is that the original author had other triggers above that trigger, and had changed the delimiter to something like "//", but had forgotten to change:

END IF;
END;
delimiter ;

To:

END IF;
END//
delimiter ;

Therefore, the reason why commenters weren't able to replicate, was because they had the correct delimiter set (";"), and as there was only one statement to delimit, didn't find any errors, as the author did. See the following test code and results, including initialisation, as some proof:

DELIMITER ; -- Just in case
DROP TABLE IF EXISTS Topics, Subjects, error_msg;

CREATE TABLE Subjects ( id INT(5) PRIMARY KEY,
                        subjectname CHAR(20));
CREATE TABLE Topics ( topicname CHAR(20),
                      subjectID INT(5)
                    # FOREIGN KEY (subjectID) REFERENCES Subjects(id) - How this check should be done...
                    );
CREATE TABLE error_msg ( Message VARCHAR(50) );

INSERT INTO Subjects VALUES
    ('5', 'Arts'),
    ('55', 'Maths'),
    ('2342', 'Biology'),
    ('12345', 'Finance');

DELIMITER $$ -- Where the delimiter would be defined, originally 
    CREATE TRIGGER InsertOnTopics
        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 ;

INSERT INTO Topics VALUES
    ('Welfare Benefits', '5'),
    ('Eigenvectors', '55'),
    ('Mitochondria', '2342'),
    ('Bank of Dad', '12345'),
    ('Something else', '555');

SELECT * FROM error_msg;

With "end;", as the original code:

mysql> SOURCE /Users/benpalmer/test.sql
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.02 sec)

Query OK, 0 rows affected (0.01 sec)

Query OK, 0 rows affected (0.02 sec)

Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

Query OK, 0 rows affected (0.02 sec)

ERROR 1064 (42000): 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 '('Something else', '555')' at line 6
Empty set (0.00 sec)

mysql>

With "END$$", in my code:

mysql> SOURCE /Users/myUsername/test.sql
Query OK, 0 rows affected (0.01 sec)

Query OK, 0 rows affected (0.01 sec)

Query OK, 0 rows affected (0.02 sec)

Query OK, 0 rows affected (0.02 sec)

Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

Query OK, 0 rows affected (0.02 sec)

Query OK, 5 rows affected (0.01 sec)
Records: 5  Duplicates: 0  Warnings: 0

+----------------------------------+
| Message                          |
+----------------------------------+
| Foreign Key Constraint Violated! |
+----------------------------------+
1 row in set (0.00 sec)

mysql>

Other than this, I don't understand why this particular example isn't being done with a proper foreign key constraint. As my first answer... Hope this helps anyone! Was also stuck on this for a while.

Answer 2

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.