Error MySQL Creating Trigger

Question

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?

khalid · Accepted Answer · 2016-09-30 12:48:05Z

2

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.

answered Sep 30, 2016 at 12:48

khalid

1755 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Error MySQL Creating Trigger

The Trigger:

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

The Trigger:

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related