12

how can this throw an error when trying to create a trigger before insert in MySQL so that it generates new UUID for every input.

CREATE TRIGGER insert_guid 
BEFORE INSERT ON guid_tool (FOR EACH  ROW 
BEGIN 
  SET NEW.guid_key = UUID()); 
END;

and here is my table 

create table guid_tool (
    ID INT NOT NULL AUTO_INCREMENT, 
    guid_key CHAR(40) NOT NULL, 
    PRIMARY KEY(ID)
) CHARSET=LATIN1;

I must be doing something wrong.

krokodilko
  • 35,300
  • 7
  • 55
  • 79
Thunder
  • 165
  • 1
  • 2
  • 12
  • And your error is that you have an [extra pair](http://dev.mysql.com/doc/refman/5.6/en/create-trigger.html) of `()`? – mabi Dec 02 '13 at 21:09
  • Removing a pair of () doesn't work either – Thunder Dec 02 '13 at 21:15
  • Then please post the error you have. [your fiddle](http://sqlfiddle.com/#!2/e8d27) runs fine. – mabi Dec 02 '13 at 21:17
  • Here are the error: 'code' ERROR 1064 (42000): 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 1 ERROR 1064 (42000): 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 'END' at line 1 – Thunder Dec 02 '13 at 21:18
  • Try to add `DELIMITER /` before the trigged definition, add `/` after `END;` and add `DELIMITER ;` after it. See this demo on SQLFiddle: http://www.sqlfiddle.com/#!2/39cbd (DELIMITER command is commented out because SQLFiddle does'nt support it). – krokodilko Dec 02 '13 at 21:25
  • Awesome :) Worked for me. So all that is because of delimiter – Thunder Dec 02 '13 at 21:31

2 Answers2

13

As mentioned by @mabi in the comments - you have a syntax error with the brackets. The following modified code works for me:

DELIMITER #
CREATE TRIGGER insert_guid 
BEFORE INSERT ON guid_tool
FOR EACH  ROW 
BEGIN 
    SET NEW.guid_key = UUID(); 
END;
#
DELIMITER ;

Testing it out:

mysql> INSERT INTO guid_tool (ID) VALUES (1);
Query OK, 1 row affected, 1 warning (0.04 sec)

mysql> SELECT * FROM guid_tool;
+----+--------------------------------------+
| ID | guid_key                             |
+----+--------------------------------------+
|  1 | a0467ebf-5c4f-11e3-903a-6cccbb4423e3 |
+----+--------------------------------------+
1 row in set (0.00 sec)
madebydavid
  • 6,457
  • 2
  • 21
  • 29
7

You can define that trigger in one statement, i.e.:

CREATE TRIGGER insert_guid BEFORE INSERT ON guid_tool FOR EACH ROW SET NEW.guid_key = UUID();
Paul Vargas
  • 41,222
  • 15
  • 102
  • 148