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.
If I update id = 1 for name = ABCD then it first read previous state of row, add it to log table.
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.
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