Syntax error when creating trigger

Question

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

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 (changing delimiter):

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'm getting: #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 '.html_id = CONCAT('f', NEW.id)' at line 6

I'm running MySQL 5.5.22.

Akash KC · Accepted Answer · 2012-07-29 16:15:22Z

3

Try with 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 ;

answered Jul 29, 2012 at 16:15

Akash KC

16.4k6 gold badges41 silver badges59 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

finspin Over a year ago

That worked, thanks! The only things you changed were that you changed the delimiter from $$ to | and typed delimiter keyword in lowercase. Is there any known issue with using $$ as delimiter?

codingbiz Over a year ago

I think the difference is the SET keyword

codingbiz · Accepted Answer · 2012-07-29 16:34:38Z

2

I think the difference is the SET keyword. try this

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 ;

answered Jul 29, 2012 at 16:34

codingbiz

26.4k9 gold badges62 silver badges99 bronze badges

Collectives™ on Stack Overflow

Syntax error when creating trigger

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related