MySQL DELIMITER syntax errors

sql mysql triggers delimiter
Sophia picture Sophia · Jul 9, 2009 · Viewed 31.1k times · Source

This MySQL script installs multiple triggers.

It works on one machine running MySQL 5.0.51b-community. On another machine running MySQL 14.12 Distrib 5.0.45, for redhat-linux-gnu (i386) it fails, with this error message, which seems to be related to the DELIMITER // ... // DELIMITER; syntax :

ERROR 1064 (42000) at line 272: 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 'DELIMITER; DROP TRIGGER IF EXISTS trigger_name; DELIMITER' at line 1

The script syntax (summarised) is:

DROP TRIGGER IF EXISTS trigger_name;
DELIMITER //
CREATE TRIGGER trigger_name BEFORE UPDATE ON table
FOR EACH ROW BEGIN
  -- Trigger logic goes here
END //
DELIMITER;

-- More trigger drop/create statements follow

What is wrong with the script, and how can I correct it?

Answer

chaos picture chaos · Jul 9, 2009

Try

DELIMITER ;

not

DELIMITER;

You're actually specifying ; as an argument to the DELIMITER command, so not having the space there may be confusing it.

Related questions

MySQL Trigger after update only if row has changed

Is there any possibility to use an "after update" trigger only in the case the data has been REALLY changed. I know of "NEW and OLD". But when using them I'm only able to compare columns. For example "NEW.count &…

Read More
MySQL Trigger - Storing a SELECT in a variable

I have a trigger in which I want to have a variable that holds an INT I get from a SELECT, so I can use it in two IF statements instead of calling the SELECT twice. How do you declare/…

Read More
Fire a trigger after the update of specific columns in MySQL

In MySQL, I want to fire a trigger after the update of specific columns. I know how to do it in Oracle and DB2: CREATE TRIGGER myTrigger AFTER UPDATE of myColumn1,myColumn2 ... ON myTable FOR EACH ROW BEGIN .... END How …

Read More