0

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?

0

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;

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.