0

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.

  • it's not that complicated but would like to understand why you want to Update it back. I mean at first 'due_date = end_date' but then 'end_date =due_date' . Also it would really help if you can share what format the due_date is in '1999-01-31 12.05.000' or '31-JAN-1999' – rrrohanb Jan 5 '16 at 5:59
  • Thanks. The format is: 2015-11-16 15:11:50 for both fields. Also the reason is: If on one page users edit the end_date then it needs to be copied to the due_date and if another user on a different page updates the due_date it needs to be copied to the end_date that's it! – mesqueeb Jan 5 '16 at 6:45
  • 1
    You haven't told us what database engine you're using (sql is a language which is very, very different from SQL Server the database engine from Microsoft). Are you using SQL Server? MySQL? Oracle? Why would due_date be a varchar rather than a datetime? If your end goal is that both columns have the same data, why do you have two separate columns? What if an update statement changes both columns? Which value wins? – Justin Cave Jan 5 '16 at 7:01
  • Thank you for your comment! I use the MySQL database! Also, the reason I need two columns is because the framework I use on one page gives bugs if it's datetime and works perfectly with a varchar, but on another page I use a framework that requires a datetime field! Also, both fields will never be available to the end user at the same time, so this should not form a problem I think. – mesqueeb Jan 5 '16 at 7:56
  • 1
    Clean up dates before getting to mysql. Don't have dates as VARCHARs. – Rick James Jan 5 '16 at 18:27
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

  • 1
    You probably can't use a simple comparison like new.due_date <> old.due_date, because either value (at least the old one) might be null. But as @YperSillyCubeᵀᴹ has suggested elsewhere, you could replace it with NOT (new.due_date <=> old.due_date). – Andriy M Jan 6 '16 at 8:47
  • I got some syntax errors like: (1064): Syntax error near 'IF new.end_date is not null and new.due_date='' then set new.due_date' at line 1 and (1064): Syntax error near 'end if' at line 1 – mesqueeb Jan 6 '16 at 8:59
  • @mesqueeb: Maybe you forgot to issue DELIMITER // before running the CREATE TRIGGER statement? – Andriy M Jan 6 '16 at 10:40
  • It worked now! Give me a few days to troubleshoot, and I'll come back to this thread. Thank you all so much. – mesqueeb Jan 6 '16 at 12:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.