NullPointer NullPointer - 7 months ago 5
SQL Question

How to get previous record after update trigger executed?

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.

Answer

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
Comments