CODEWITHSUNDEEP
mysqlsql
finspin
finspin

Reputation: 4071

Syntax error when creating trigger

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.

Upvotes: 4

Views: 924

Answers (2)

codingbiz
codingbiz

Reputation: 26406

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 ;

Upvotes: 2

Akash KC
Akash KC

Reputation: 16310

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 ;

Upvotes: 3

Related Questions