How do you perform a MYSQL After Update Trigger Insert Loop?

Question

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?

• Krismorte: You can try build a cursor mysqltutorial.org/mysql-cursor

Answer 1

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;