Simple trigger to update two columns in one table

Question

I want to create a trigger MySQL that updates two fields at the same time.

Whenever a record is added or inserted, I want the contents to be copied to one field or the other, depending on which one is empty/edited.

due_date is a varchar and end_date is a datetime.

e.g.

1. IF new record with due_date filled in but end_date empty → copy due_date to end_date after insert.
2. IF new record with end_date filled in but due_date empty → copy end_date to due_date after insert.
3. IF update to due_date → copy that to end_date after update
4. IF update to end_date → copy that to due_date after update

Please note, the due_date format is already the correct and same format as datetime.

Answer 1

Hope i have understand what you are saying correctly, following would be the code you may be looking for.

DELIMITER //
CREATE TRIGGER trig_1 before insert
ON  <table_name> FOR EACH ROW
BEGIN

   IF   new.due_date is not null and new.end_date='' then
        set new.end_date=new.due_date;
   end if;
      IF   new.end_date is not null and new.due_date='' then
        set new.due_date=new.end_date;
   end if;

END;
//


DELIMITER //

CREATE TRIGGER trig_2 before update
ON  <table_name> FOR EACH ROW
BEGIN 

   IF   new.due_date <>old.due_date then
        set new.end_date=new.due_date;
   end if;
      IF   new.end_date <> old.end_date then
        set new.due_date=new.end_date;
   end if;

END;
//

Hope it helps