MySQL Trigger: IF ELSE error

Question

Trying to execute first ever trigger which should insert value of one table to another only if the data is new. Below is my code:

BEGIN
    DECLARE email VARCHAR(30);
   INSERT INTO data_audit SET data_audit_id = OLD.id;
   IF (NEW.email = OLD.email) THEN
    email = NULL;
   ELSE
    email = OLD.email;
   END IF;
   UPDATE corporate_audit SET email = email WHERE corporate_audit_id = last_insert_id();
END

I am getting following error:

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 '= NULL; ELSE email = OLD.email; END IF; UPDATE corporate_audit SET ' at line 5

And when I execute code without any IF-THEN block and store OLD.email then it just insert new ID in audit table but does not update field. Please guide me

John Woo · Accepted Answer · 2012-12-18 14:51:39Z

7

you are missing SET

   IF (NEW.email = OLD.email) THEN
      SET email = NULL;
   ELSE
      SET email = OLD.email;
   END IF;

answered Dec 18, 2012 at 14:51

John Woo

265k70 gold badges509 silver badges500 bronze badges

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

3 Comments

Volatil3 Over a year ago

Awesome. Thanks Bro. I really love my Stackoverflow family that is always up for selfless help.

Volatil3 Over a year ago

Sorry I am bothering you again. Since I am using AFTER UPDATE trigger, it is not letting me to update column value of the table that is being triggered.

Shafizadeh Over a year ago

If I have three conditions, what should I do ? I need to elseif, there is elseif in the mysql (trigger) ?

Collectives™ on Stack Overflow

MySQL Trigger: IF ELSE error

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related