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;

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