2

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;
  • Could you please post the table definitions of the two tables promotion and produit? Thank you. – Dario Apr 11 '17 at 13:22
  • yes sure..... promotion('id_promotion' , 'percentage' , 'id_produit')-------product('id_product' , 'price' ')............... and thank you for your interest – user122588 Apr 11 '17 at 16:28
0

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).

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.