Mysql – How to perform a MYSQL After Update Trigger Insert Loop

insertMySQLtriggerupdate

I have 3 mysql tables, one called batches, one called base_formula and one called raw_materials_stock.

I want to perform an after update trigger on the batches table to insert information into the raw_materials_stock table based on information from the base_formula table.

Batches Table

batch_id, complete_dt, base_id

Base Formula Table

base_id, raw_material_id, percentage

Raw Material Stock Table

batch_id,raw_material_id,use_date,quantity

Batches Trigger

IF OLD.complete_dt is NULL and NEW.complete_dt is not null and (select b.raw_material_id from base_formula b where b.base_id=new.base_id) is not null  THEN 

INSERT INTO raw_materials_stock (batch_id,raw_material_id,use_date,quantity) 
VALUES(
new.batch_id,
(select b.raw_material_id from base_formula b where b.base_id=new.base_id),
now(),
(select ((new.volume/100)*b.percent)*-1 from base_formula b where b.base_id=new.base_id)); 

END IF

Issue

This issue i have is the base_formula table has more than 1 row with the same base_id. How can i use a loop to insert all of the rows with the same base_id into the raw_material_stock table?

Best Answer

You can restructure your query

INSERT INTO raw_materials_stock 
  (batch_id,raw_material_id,use_date,quantity) 
    SELECT 
       new.batch_id, 
       b.raw_material_id, 
       now(), 
       ((new.volume/100)*b.percent)*-1 
    from base_formula b where b.base_id=new.base_id;
Related Question