SQL - Create trigger after insert

Question

I have these four table that stores menus and the ingredients that if add a row to customer_order the raw_material_quantity from raw_materials should be automatically deducted based on the quantity from menu_inventory times the order_quantity from customer_order.

Table menu

+-----------+-----------+
| menu_code | menu_name |
+-----------+-----------+
|  prod-001 | Ice cream |
+-----------+-----------+

Table raw_materials

+-------------------+-------------------+-----------------------+
| raw_material_code | raw_material_name | raw_material_quantity |
+-------------------+-------------------+-----------------------+
|     item-001      |       sugar       |          10           |
+-------------------+-------------------+-----------------------+
|     item-002      |       cream       |          20           |
+-------------------+-------------------+-----------------------+

Table menu_inventory

+-----------------------+-------------------+-----------+----------+
| raw_ingredients_number| raw_material_code | menu_code | quantity |
+-----------------------+-------------------+-----------+----------+
|           1           |     item-001      | prod-001  |    5     |
+-----------------------+-------------------+-----------+----------+
|           2           |     item-002      | prod-001  |    10    |
+-----------------------+-------------------+-----------+----------+

Table customer_order

+-------------------+--------------+-----------+----------------+
| customer_order_no |  customer_no | menu_name | order_quantity |
+-------------------+--------------+-----------+----------------+
|         1         | customer-001 | Ice Cream |        2       |
+-------------------+------------- +-----------+----------------+

So I want to create a trigger to table customer_order after insert. I already created one but it does not provide accurate results. Any answers will be a big help thanks.

Edit

    CREATE DEFINER=`root`@`localhost` TRIGGER `customer_order_AFTER_INSERT` AFTER INSERT ON `customer_order` FOR EACH ROW BEGIN
        declare x int;
        declare y int;
        declare quantity_1 int;
        declare quantity_2 int;
        declare sums int;

         if (select count(raw_material_code) from menu_inventory where  menu_code = new.menu_code > 0)
 then           
            set y = new.order_quantity;

            DROP TEMPORARY TABLE IF EXISTS `temptable`;
            CREATE TEMPORARY TABLE temptable (select row_number() over() as raw_ingredients_number,quantity,raw_material_code  from menu_inventory  where menu_code = new.menu_code);        
            while y > 0 do
            set x = (select count(raw_material_code ) from menu_inventory where menu_code = new.menu_code);
                while x > 0 do

                    set @mcode = (select menu_code from temptable where menu_ingredients_number = x);
                    set @rcode = (select raw_material_code from raw_material where raw_material_name  = @mcode);
                    set quantity_1 = (select raw_material_quantity  from raw_material where raw_material_name  = @mcode);
                    set quantity_2 = (select quantity from temptable where menu_ingredients_number = x);
                    set sums = quantity_1-quantity_2;

                    update raw_material set raw_material_quantity  = sums where raw_material_code  = @rcode;
                    set x=x-1;
                end while;

                set y=y-1;
            end while;

         end if;
    END 

Answer 1

A much cleaner method would be to use a multi-table update in the trigger

drop trigger if exists  `customer_order`;
delimiter $$
CREATE  TRIGGER `customer_order_AFTER_INSERT` AFTER INSERT ON `customer_order` 
 FOR EACH ROW 
 BEGIN
    update raw_materials r
        join menu m on m.menu_name = new.menu_name
        join menu_inventory mi on mi.menu_code = m.menu_code
            set r.raw_material_quantity = r.raw_material_quantity - (new.Order_quantity * mi.quantity)
        where r.raw_material_code = mi.raw_material_code;

 end $$

 delimiter ;