时间戳

首页 > 解决方案 > SQL - 插入后创建触发器

mysql - SQL - 插入后创建触发器

问题描述

我有这四个存储菜单和成分的表，如果在 from 中添加一行，则customer_order应该根据from时间 order_quantity from自动扣除。raw_material_quantityraw_materialsquantitymenu_inventorycustomer_order

表格菜单

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

表原材料

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

表menu_inventory

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

表 customer_order

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

所以我想customer_order在插入后为表创建一个触发器。我已经创建了一个，但它没有提供准确的结果。任何答案都会有很大帮助，谢谢。

编辑

    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

标签： mysqlsqldatabase-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 ;

推荐阅读