创建触发器MYSQL PHPMyadmin时出错

Question

Hi this is my first Question here so I'm just posting some of my code here if you need some more information on the problem please ask.

I'm trying to create a trigger is MYSQL via PHPMyadmin (yes I know not the best tool). The idea is to be able to clean a string before the insert query executes. Simple enough.

My code:

CREATE TRIGGER `CLEAR` BEFORE INSERT ON `TABLE` 
FOR EACH ROW BEGIN
    SET NEW.LNAME = REPLACE(NEW.LNAME,'?','');
END;

However I keep getting this error message

#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 3 

I have no idea why. any ideas are welcome thanks before hand. Also excuse my not so good ise of the english language, I'm not a native speaker.

Answer 1

You've got to change the DELIMITER before the CREATE statement and set it back afterwards:

DELIMITER |
CREATE TRIGGER `CLEAR` BEFORE INSERT ON `TABLE` 
FOR EACH ROW BEGIN
    SET NEW.LNAME = REPLACE(NEW.LNAME,'?','');
END |
DELIMITER ;

otherwise you could write your trigger so it needs no delimiter:

CREATE TRIGGER `CLEAR` BEFORE INSERT ON `TABLE` 
FOR EACH ROW
    SET NEW.LNAME = REPLACE(NEW.LNAME,'?','')

Because there's only one statement executed in the trigger body, you don't have to enclose it in a BEGIN... END block and have no need of the semicolon to enc the statement too.