Loop in trigger after insert

Question

I accidently deleted my trigger (Needed to reset something in my DB). But i don't know how to make my trigger again..

I have a 2 tables.

1:

train_information:

2:

axle:

Now, when a train gets added and inserted in the DB table: train_information. I want a trigger to also update the axle table.

After the trigger worked i want the axle table to look like:

However. If the train_information has 4 axles. i only want to put 3 in the axle table. So i want the loop/trigger to insert 1 less. So if the train_information table has 20 axles. i want the axle table to show 19 etc.

Answer 1

You can have the following trigger as

delimiter //
create trigger train_information_ins after insert on train_information
for each row 
begin
 declare x int ;
 if(new.number_of_axies >  0 ) then
   set x = 1 ;
   while x < new.number_of_axies do
     insert into axle (train_id,axle)
     values
     (new.train_id,x);
     set x=x+1;
    end while ;
  end if ;
end;//

delimiter ;

Answer 2

Below script will get you going. This script will loop through the number of axles which got inserted.

DELIMITER //

CREATE TRIGGER `train_information_after_insert` AFTER INSERT ON `train_information` 
FOR EACH ROW
BEGIN

DECLARE noAxles INT;
SET noAxles = NEW.number_of_axles;

myLoop: LOOP
  SET noAxles = noAxles - 1;

  //UPDATE STATEMENT FOR AXLE TABLE HERE

  IF noAxles = 0 THEN
    LEAVE myLoop;
  END IF;
END LOOP myLoop;

END//

DELIMITER ;