title: MySQL trigger to insert join result of two tables into a third
body: <p>I have 4 tables, product, promotion, product_promotion and production_promotion_denormalized. In my products table I have:</p>
  
  <pre><code>product_id  product_name
      1        Product A
      2        Product B
  </code></pre>
  
  <p>For promotions:</p>
  
  <pre><code>promotion_id  promotion_name
      1        Promo A
      2        Promo B
  </code></pre>
  
  <p>My third table is an associative table, when ever a promotion is created on a product, the associative table gets an entry with the product id and promotion id. So there can be multiple products with multiple promotions and for each combination of product and promotion there will be an entry in this table:</p>
  
  <pre><code>id   product_id   promotion_id
  1        1            1
  2        1            2
  3        2            2   
  4        2            1
  </code></pre>
  
  <p>My fourth table is a denormalized version, what i want to do is write a trigger in mysql to get product id and its details, along with promotion id and its details and insert them in my fourth table:</p>
  
  <pre><code>id   product_id   product_name   promotion_id   promotion_name
  1        1           Product A        1            Promo A
  2        1           Product A        2            Promo B
  3        2           Product B        2            Promo B
  4        2           Product B        1            Promo A
  </code></pre>
  
  <p>I need to write this trigger for on insert and on update in mysql.
  Please help.</p>
  
  <p>Thanks in advance.</p>