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;
Aucun commentaire:
Enregistrer un commentaire