mysql trigger update after insert

Question

I need help to create a trigger that can calculate prices after promotions. I have two tables "product" and "promotion". after inserting in promotion table the promotion price and the product id, I want to update the price in product table to the new price after promotion. here is my try :

 CREATE TRIGGER update_promotion
 AFTER INSERT ON promo
 SELECT @prix_promo = prix_promotion FROM INSERTED; 
 SELECT @id = id_prod FROM INSERTED;
 SELECT @prix_sanspromo = prix FROM produit 
 WHERE id_produit = @id;
 FOR EACH ROW
 UPDATE produit SET prix_promo=((@prix_promo*prix)/100) WHEREid_produit = @id;

• Dario: Could you please post the table definitions of the two tables promotion and produit? Thank you.
• user122588: yes sure..... promotion('id_promotion' , 'percentage' , 'id_produit')-------product('id_product' , 'price' ')............... and thank you for your interest

Answer 1

This is an answer which follows the table definitions you posted:

CREATE TRIGGER update_promotion
 AFTER INSERT ON promotion 
 FOR EACH ROW 
 UPDATE product 
 -- Here you are losing the old price!
 SET price = NEW.percentage * price / 100, 
 WHERE id_product = NEW.id_produit;

However, the example you posted does not match the column names you gave later. This means you are not posting your actual code, and this makes everybody’s job harder.

Maybe this is only a toy example to learn how to use MySQL triggers. As such, it’s fine. In production, the very idea to change the price in the main product table upon insertion of a new promotion is questionable. A better design would be not to change the product table, add start_date and end_date columns to the promotion table, and read the current prices either from a view which applies the active promotions for the day, or, if you have millions of products and performance becomes an issue, from a temporary table regenerated each day (and possibly replicated to slave servers... you know your scale).