log mysql updates

Question

Hi to all,

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

I usually update the existing records.I was wondering how i can log the updates like below

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

Whitch means that the productID 125 had title Products1 and became test after update and had price : 125 and 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.

I was wondering if any other method exist.

Thanks

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 ;