lundi 1 décembre 2014

MySQL: Adding an IF condition to trigger?


We've got a trigger that is overwriting the uuid value before insert.


The vers.1 of our API depended on the DB to generate the UUID's, but I'm writing v2 which is using a library that generates the UUID's in the code before sending the INSERT query.

As such, I need to update our trigger so that the new uuid's created via my Model can get stored without being overwritten.


I'm working in PhpMyAdmin (for ease) and our original trigger looks like this:



DROP TRIGGER IF EXISTS `init_uuid_company`//
CREATE TRIGGER `init_uuid_m3_company` BEFORE INSERT ON `company`
FOR EACH ROW SET NEW.uuid = UUID()
//


Obviously, the delimiter is set to //.


My latest attempt looks like:



DROP TRIGGER IF EXISTS `init_uuid_company`//
CREATE TRIGGER `init_uuid_company` BEFORE INSERT ON `company`
FOR EACH ROW BEGIN
IF(NEW.uuid IS NULL OR NEW.uuid = '') THEN
SET NEW.uuid = UUID();
END IF;
//


But it's throwing an error saying You have an error in your SQL syntax ... near '' at line 5


Unless I'm counting wrong, line 5 is the SET line. No quotes used there.

What am I missing?





Aucun commentaire:

Enregistrer un commentaire