0

I have something different scenario to work with Trigger.

I want to insert record in log tables before actually update on main table. But I want to do it differently.

Like only add records into log tables when really updates happen in main table and I want to do it using MySQL Trigger.

I am attaching screen shots here.

enter image description here

If I update id = 1 for name = ABCD then it first read previous state of row, add it to log table.

enter image description here

What I want to check is using MySQL trigger that it only adds to main_table_log table when actual update happens to main_table. If no updates in main_table then it should not add record to main_table_log table.

Can anyone help me for that? I think it can be do-able but not getting how to do it?

And obviously I am using PHP as front.

Smile
  • 2,770
  • 4
  • 35
  • 57

1 Answers1

2

You can test whether the old and new values are different in the trigger.

CREATE TRIGGER log_trigger
AFTER UPDATE ON main_table
FOR EACH ROW
    IF OLD.name != NEW.name OR OLD.num != NEW.num
    THEN 
        INSERT INTO main_table_log (id, name, num) VALUES (OLD.id, OLD.name, OLD.num)
    END IF
Barmar
  • 741,623
  • 53
  • 500
  • 612
  • Thank you very much for quick reply. Instead checking each field may I know if OLD record is actually updated? Because I have around 10-15 fields in each tables. – Smile Apr 23 '16 at 07:07
  • 1
    Do you have a timestamp column with `ON UPDATE CURRENT_TIMESTAMP`? Then you can just check that column, because it won't be updated if none of the other columns are updated. See http://stackoverflow.com/questions/6296313/mysql-trigger-after-update-only-if-row-has-changed – Barmar Apr 23 '16 at 07:08
  • I am looking at the link which you provided. I first tried to find related question but could not found. – Smile Apr 23 '16 at 07:15
  • Hi @Barmar, I am done with you answer and so accepting it. Thank you so much for being kind and quick answer. You saved me for many hours (y) ;) – Smile Apr 23 '16 at 07:34