History field in MySQL database

Question

Trying to create a field in a table that will get the old value of another field. My table is very similar to this:

 ------------ -------------------- ------------------------ 
| id int(10) | price decimal(5,2) | price_old decimal(5,2) | 
 ----------------------------------------------------------

What I am trying to get is the value of price field to get copied in price_old field ON UPDATE of the current record.

Is it possible to achieve this only with mysql?

PS: The data contained in that cell is not critical. The usage is to store the previous price of a item and after to be able to show how it changed. I will only need the last value, not the full history of the table entry. (My MySQL server is 5.0, if this matters)

Answer 1

Use triggers on update rule. There set value of price to price_old

CREATE TRIGGER `update_price_old`  
    BEFORE UPDATE ON `table_name` FOR EACH ROW  
    BEGIN 
    UPDATE `table_name` SET price_old = OLD.price where OLD.id = id;
    END 

Answer 2

Yes, you can use a BEFORE UPDATE trigger (documentation).

I think this syntax is right but haven't used MySQL in a while so you may have to toy with it. If it's wrong and you get it working please let me know so I can edit it.

DELIMITER $$
CREATE TRIGGER `price_trigger`
BEFORE UPDATE ON `products`
FOR EACH ROW
BEGIN
    IF NEW.price != OLD.price
        SET price_old = OLD.price;
    END IF;
END$$
DELIMITER ;