log mysql updates

Question

I have a migration script that reads from one DB and write to a second DB.

I usually update the existing records. How can I log the updates like:

productID : 125
title : Product1 => test update
price : 125 => 140

This means that the productID 125 had title "Products1" and became "test" after update and had price "125" which became "140"

One thought is to read the record keep the values and then update, read again the values and the compare and log what necessary fields.

Do any other methods exist?

Answer 1

You could use a trigger and store the changes in another table.

From the top of my head (the following assumes that productId never will be updated);

create table main (
    `id` int not null auto_increment,
    `title` varchar(30) not null,
    `price` float not null, 
    primary key(`id`)
);

create table logger (
    `id` int not null auto_increment,
    `productId` int not null,
    `from_title` varchar(30) not null,
    `to_title` varchar(30) not null,
    `from_price` float not null,
    `to_price` float not null,
    primary key(`id`)
);

delimiter //
create trigger my_logger before update on main
begin
    insert into
        logger
    set
        `productId`=OLD.`id`,
        `from_title`=OLD.`title`,
        `to_title`=NEW.`title`,
        `from_price`=OLD.`price`,
        `to_price`=NEW.`title`;
end;//
delimiter ;