Error creating trigger

This is a simple trigger I'm trying to create:

CREATE TRIGGER add_item_id BEFORE INSERT ON products 
FOR EACH ROW
BEGIN
DECLARE max_id INTEGER;
SELECT MAX(item_id) INTO @max_id FROM products;
SET NEW.item_id = @max_id + 1;
END;

I tried it both on phpMyAdmin SQL window and mysql prompt and get the same error as below:

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

---

delimiter //
CREATE TRIGGER add_item_id BEFORE INSERT ON products 
FOR EACH ROW
BEGIN
DECLARE max_id int;
SELECT MAX(item_id) INTO max_id FROM products;
SET NEW.item_id = max_id + 1;
END//
delimiter ;

Some notes:

• If you declare (local variable) max_id, use it. @max_id is a GLOBAL variable. Any @variable can be used without declaring it, but it stays with the session as long as the session lives.
• Your code is fine, you are just missing the delimiter changes. Without delimiter //, MySQL sees the CREATE TRIGGER statement ending at ..FROM PRODUCTS;, which makes it invalid

---

You could also do:

CREATE TRIGGER add_item_id
  BEFORE INSERT
  ON products 
FOR EACH ROW
BEGIN
  SET NEW.item_id = 1 + ( SELECT MAX(item_id)
                          FROM products
                        ) ;
END;

---

Note: you can declare auto_incremented fields in almost all RDBMS.