MySQL: Adding an IF condition to trigger?

Posted on

Question :

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?

Answer :

You forgot END //

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;
 END //

Leave a Reply

Your email address will not be published.