Geek Answers Handbook

Syntax error while creating trigger

I am trying to create a new trigger in MySQL, but no matter what I try, I get a syntax error. I want to insert a value into the html_id column, which would be a concatenation of the letter f and the id column:

 CREATE TRIGGER htmlid BEFORE INSERT ON makelist_food FOR EACH ROW BEGIN IF (NEW.html_id IS NULL) THEN NEW.html_id = CONCAT('f', NEW.id); END IF; END

I also tried this:

 CREATE TRIGGER htmlid BEFORE INSERT ON makelist_food FOR EACH ROW BEGIN IF (NEW.html_id IS NULL) THEN INSERT INTO makelist_food SET html_id = CONCAT('f', NEW.id); END IF; END

And this (separator change):

 DELIMITER $$ CREATE TRIGGER htmlid BEFORE INSERT ON makelist_food FOR EACH ROW BEGIN IF (NEW.html_id IS NULL) THEN NEW.html_id = CONCAT('f', NEW.id); END IF; END$$ DELIMITER ;

The error I get is: # 1064 - You have an error in the SQL syntax; check the manual that matches your version of MySQL server for the correct syntax to use next to .html_id = CONCAT ('f', NEW.id) 'on line 6

I am running MySQL 5.5.22.

+4
source share
2 answers

Try using this query:

 delimiter | CREATE TRIGGER htmlid BEFORE INSERT ON makelist_food FOR EACH ROW BEGIN IF (NEW.html_id IS NULL) THEN INSERT INTO makelist_food SET html_id = CONCAT('f', NEW.id); END IF; END | delimiter ;
+2
source

I think the difference is the SET keyword. try it

 DELIMITER $$ CREATE TRIGGER htmlid BEFORE INSERT ON makelist_food FOR EACH ROW BEGIN IF (NEW.html_id IS NULL) THEN SET NEW.html_id = CONCAT('f', NEW.id); END IF; END$$ DELIMITER ;
+2
source

More articles:


All Articles