mysql - 库存的订单触发(更新)

Question

我还是 mySQL 的初学者。我一直在试图找出我的项目的触发因素，但无法找到解决方案。这是我正在尝试做的事情:

下订单后，我想要一个触发器来更新库存。这里的问题是订单可以包含许多产品，而这些产品由库存的不同元素组成。这是我的尝试，也许它可以帮助您理解这个想法。

create trigger subtract_inv
after insert on order_details for each row
begin
update inventory
set Inv_qty = Inv_qty - (order_details.Order_details_qty * recipes.Requiered_qty) 
where  Products_Prod_ID = NEW.Products_Prod_ID and inventory.Inv_ID = recipes.Inventory_Inv_ID;
end$$
delimiter ;

这是 3 个表

     -- -----------------------------------------------------
        -- Table `McDo`.`Order_details`
        -- -----------------------------------------------------
        CREATE TABLE IF NOT EXISTS `McDo`.`Order_details` (
          `orders_Order_id` INT NOT NULL,
          `Products_Prod_ID` INT NOT NULL,
          `Order_details_qty` INT NULL,
          PRIMARY KEY (`orders_Order_id`, `Products_Prod_ID`),
          INDEX `fk_orders_has_Products_Products1_idx` (`Products_Prod_ID` 
  ASC) VISIBLE,
          INDEX `fk_Order_details_orders1_idx` (`orders_Order_id` ASC) 
       VISIBLE,
          CONSTRAINT `fk_orders_has_Products_Products1`
            FOREIGN KEY (`Products_Prod_ID`)
            REFERENCES `McDo`.`Products` (`Prod_ID`)
            ON DELETE NO ACTION
            ON UPDATE NO ACTION,
          CONSTRAINT `fk_Order_details_orders1`
            FOREIGN KEY (`orders_Order_id`)
            REFERENCES `McDo`.`Orders` (`Order_id`)
            ON DELETE NO ACTION
            ON UPDATE NO ACTION)
        ENGINE = InnoDB;
        -- -----------------------------------------------------
        -- Table `McDo`.`Inventory`
        -- -----------------------------------------------------
        CREATE TABLE IF NOT EXISTS `McDo`.`Inventory` (
          `Inv_ID` INT NOT NULL,
          `Inv_type` VARCHAR(1) NOT NULL,
          `Inv_name` VARCHAR(45) NULL,
          `Inv_qty` INT NULL,
          `Inv_price_unit` DECIMAL(20,2) NULL,
          PRIMARY KEY (`Inv_ID`))
        ENGINE = InnoDB;
        -- -----------------------------------------------------
        -- Table `McDo`.`Recipes`
        -- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `McDo`.`Recipes` (
  `Products_Prod_ID` INT NOT NULL,
  `Inventory_Inv_ID` INT NOT NULL,
  `Requiered_qty` INT NULL,
  PRIMARY KEY (`Products_Prod_ID`, `Inventory_Inv_ID`),
  INDEX `fk_Products_has_Inventory_Products1_idx` (`Products_Prod_ID` ASC) 
 VISIBLE,
  INDEX `fk_Products_has_Inventory_Inventory1_idx` (`Inventory_Inv_ID` 
 ASC) VISIBLE,
  CONSTRAINT `fk_Products_has_Inventory_Products1`
    FOREIGN KEY (`Products_Prod_ID`)
    REFERENCES `McDo`.`Products` (`Prod_ID`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_Products_has_Inventory_Inventory1`
    FOREIGN KEY (`Inventory_Inv_ID`)
    REFERENCES `McDo`.`Inventory` (`Inv_ID`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


and here a sample of the data

-- -----------------------------------------------------
-- Data for table `McDo`.`Recipes`
-- -----------------------------------------------------
START TRANSACTION;
USE `McDo`;
INSERT INTO `McDo`.`Recipes` (`Products_Prod_ID`, `Inventory_Inv_ID`, `Requiered_qty`) VALUES (301, 102, 3);
INSERT INTO `McDo`.`Recipes` (`Products_Prod_ID`, `Inventory_Inv_ID`, `Requiered_qty`) VALUES (301, 103, 2);
INSERT INTO `McDo`.`Recipes` (`Products_Prod_ID`, `Inventory_Inv_ID`, `Requiered_qty`) VALUES (301, 112, 1);
INSERT INTO `McDo`.`Recipes` (`Products_Prod_ID`, `Inventory_Inv_ID`, `Requiered_qty`) VALUES (301, 110, 1);
INSERT INTO `McDo`.`Recipes` (`Products_Prod_ID`, `Inventory_Inv_ID`, `Requiered_qty`) VALUES (301, 111, 2);
INSERT INTO `McDo`.`Recipes` (`Products_Prod_ID`, `Inventory_Inv_ID`, `Requiered_qty`) VALUES (301, 114, 1);
INSERT INTO `McDo`.`Recipes` (`Products_Prod_ID`, `Inventory_Inv_ID`, `Requiered_qty`) VALUES (301, 113, 1);
INSERT INTO `McDo`.`Recipes` (`Products_Prod_ID`, `Inventory_Inv_ID`, `Requiered_qty`) VALUES (301, 108, 1);
INSERT INTO `McDo`.`Recipes` (`Products_Prod_ID`, `Inventory_Inv_ID`, `Requiered_qty`) VALUES (302, 101, 2);
INSERT INTO `McDo`.`Recipes` (`Products_Prod_ID`, `Inventory_Inv_ID`, `Requiered_qty`) VALUES (302, 111, 2);
INSERT INTO `McDo`.`Recipes` (`Products_Prod_ID`, `Inventory_Inv_ID`, `Requiered_qty`) VALUES (302, 103, 2);
INSERT INTO `McDo`.`Recipes` (`Products_Prod_ID`, `Inventory_Inv_ID`, `Requiered_qty`) VALUES (302, 107, 1);
INSERT INTO `McDo`.`Recipes` (`Products_Prod_ID`, `Inventory_Inv_ID`, `Requiered_qty`) VALUES (302, 113, 1);
INSERT INTO `McDo`.`Recipes` (`Products_Prod_ID`, `Inventory_Inv_ID`, `Requiered_qty`) VALUES (302, 114, 1);
INSERT INTO `McDo`.`Recipes` (`Products_Prod_ID`, `Inventory_Inv_ID`, `Requiered_qty`) VALUES (302, 108, 1);
INSERT INTO `McDo`.`Recipes` (`Products_Prod_ID`, `Inventory_Inv_ID`, `Requiered_qty`) VALUES (302, 109, 1);
INSERT INTO `McDo`.`Recipes` (`Products_Prod_ID`, `Inventory_Inv_ID`, `Requiered_qty`) VALUES (301, 204, 1);
INSERT INTO `McDo`.`Recipes` (`Products_Prod_ID`, `Inventory_Inv_ID`, `Requiered_qty`) VALUES (302, 202, 1);
-- -----------------------------------------------------
-- Data for table `McDo`.`Inventory`
-- -----------------------------------------------------
START TRANSACTION;
USE `McDo`;
INSERT INTO `McDo`.`Inventory` (`Inv_ID`, `Inv_type`, `Inv_name`, `Inv_qty`, `Inv_price_unit`) VALUES (101, 'C', 'regular bun', 1000, 0.07);
INSERT INTO `McDo`.`Inventory` (`Inv_ID`, `Inv_type`, `Inv_name`, `Inv_qty`, `Inv_price_unit`) VALUES (102, 'C', 'sesame bun', 1000, 0.12);
INSERT INTO `McDo`.`Inventory` (`Inv_ID`, `Inv_type`, `Inv_name`, `Inv_qty`, `Inv_price_unit`) VALUES (103, 'C', 'beef patty', 1000, 0.35);
INSERT INTO `McDo`.`Inventory` (`Inv_ID`, `Inv_type`, `Inv_name`, `Inv_qty`, `Inv_price_unit`) VALUES (107, 'C', 'ketchup sachet', 5000, 0.02);
INSERT INTO `McDo`.`Inventory` (`Inv_ID`, `Inv_type`, `Inv_name`, `Inv_qty`, `Inv_price_unit`) VALUES (108, 'C', 'seasoning sachet', 5000, 0.01);
INSERT INTO `McDo`.`Inventory` (`Inv_ID`, `Inv_type`, `Inv_name`, `Inv_qty`, `Inv_price_unit`) VALUES (109, 'C', 'mustard sachet', 5000, 0.01);
INSERT INTO `McDo`.`Inventory` (`Inv_ID`, `Inv_type`, `Inv_name`, `Inv_qty`, `Inv_price_unit`) VALUES (110, 'C', 'big mac sauce sachet', 5000, 0.04);
INSERT INTO `McDo`.`Inventory` (`Inv_ID`, `Inv_type`, `Inv_name`, `Inv_qty`, `Inv_price_unit`) VALUES (111, 'C', 'cheese slice', 2000, 0.05);
INSERT INTO `McDo`.`Inventory` (`Inv_ID`, `Inv_type`, `Inv_name`, `Inv_qty`, `Inv_price_unit`) VALUES (112, 'C', 'precut letuce', 1500, 0.02);
INSERT INTO `McDo`.`Inventory` (`Inv_ID`, `Inv_type`, `Inv_name`, `Inv_qty`, `Inv_price_unit`) VALUES (113, 'C', 'precut onion', 1500, 0.01);
INSERT INTO `McDo`.`Inventory` (`Inv_ID`, `Inv_type`, `Inv_name`, `Inv_qty`, `Inv_price_unit`) VALUES (114, 'C', 'precut pickle', 1500, 0.01);
INSERT INTO `McDo`.`Inventory` (`Inv_ID`, `Inv_type`, `Inv_name`, `Inv_qty`, `Inv_price_unit`) VALUES (202, 'N', 'mc double wrapping', 1000, 0.01);
INSERT INTO `McDo`.`Inventory` (`Inv_ID`, `Inv_type`, `Inv_name`, `Inv_qty`, `Inv_price_unit`) VALUES (204, 'N', 'big mac box', 1000, 0.02);

订单可以如下所示:

1000,301,2;
1000,302,1;
1001,301,2;
1002,302,3;

Answer 1

您可以在更新查询中使用联接，所以也许这样

delimiter $$
CREATE TRIGGER `subtract_inv` AFTER INSERT ON `order_details` FOR EACH ROW 
begin
    update inventory join 
    (select r.inventory_inv_id rinvid,new.order_details_qty * r.Requiered_qty adjustment
    from recipes r join inventory i  on i.inv_id  = r.inventory_inv_id 
    where r.products_prod_id = new.products_prod_id) s
    set inv_qty = inv_qty - s.adjustment
    where inventory.Inv_ID = s.rinvid;
end $$
delimiter ;