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.
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.