2

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;
  • Put the prices in a separate, parallel, table. This will speed things up, possibly allow for simpler UPDATEs, and be cleaner design. – Rick James Feb 10 '15 at 5:16
3

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 ;
  • Not quite the result I was looking for. This changes both the unit_price and the last_price to the updated value. I would like to set last_price = old unit_price and then have unit_price updated to new unit_price. Is this not possible? – JBLucky24 Feb 9 '15 at 16:17
  • I updated it with OLD. instead of NEW. – RolandoMySQLDBA Feb 9 '15 at 16:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.