MySQL – BEFORE UPDATE Trigger to Set Column A to Column B’s Value

MySQLtrigger

I have a nightly scheduled event which updates all rows in 'products' table with the newest 'unit_price' from a price list table where 'price_list.active-date' is >= the CURDATE(). What I need to do is create a trigger for the products table to set the field 'last_price' to the old unit_price before the update only on rows where unit_price is changing. Is this possible.

Scheduled Event:

CREATE EVENT `update_active_price` ON SCHEDULE EVERY 1 DAY 
    STARTS '2015-02-09 00:00:00' 
    ON COMPLETION PRESERVE 
    ENABLE COMMENT 'updates nightly at midnight' 
    DO 
        UPDATE products p 
        JOIN price_list pr ON p.product_id = pr.product_id
        SET p.unit_price = pr.amount
        WHERE active_date = CURDATE();

Currently non-working Trigger:

CREATE TRIGGER `update_last_price` BEFORE UPDATE ON `products`
FOR EACH ROW UPDATE products p
SET p.last_price = p.unit_price;

Best Answer

Adjust the trigger's syntax

DROP TRIGGER IF EXISTS `update_last_price`;
CREATE TRIGGER `update_last_price`
BEFORE UPDATE ON `products`
FOR EACH ROW
SET NEW.last_price = OLD.unit_price;

or if the trigger is to have multiple lines, do this

DROP TRIGGER IF EXISTS `update_last_price`;
DELIMITER $$
CREATE TRIGGER `update_last_price`
BEFORE UPDATE ON `products`
FOR EACH ROW
BEGIN
    SET NEW.last_price = OLD.unit_price;
END $$
DELIMITER ;
Related Question