Felipe S. Bueno Felipe S. Bueno - 2 months ago 6
MySQL Question

Error MySQL Creating Trigger

I'm getting an odd error every time I try to create one UPDATE trigger into one of my tables...

The Trigger:



CREATE TRIGGER upd_data_nascimento BEFORE UPDATE ON pfi_pessoa_fisica
FOR EACH ROW
BEGIN
IF OLD.date_of_birth = '0000-00-00' THEN
SET NEW.date_of_birth = NULL;
END IF;
END;


And the error:


Error SQL (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 5


Any ideas?

Answer

Make sure you are using delimiter

CREATE TRIGGER upd_data_nascimento BEFORE UPDATE ON abc_table
FOR EACH ROW
  BEGIN
     IF OLD.date_of_birth = '0000-00-00' THEN
        SET NEW.date_of_birth = NULL;
     END IF;
  END

If you are executing it in PHPMyAdmin then set the delimiter to # or something else. And then execute it will work fine I have tested it with above code.

Comments