mysql - 使用触发器计算金额..但无法获取价格值

Question

我有下表:

Table Goods_has_Taker(order_id, good_id...)
Table Goods (good_id, price...)
Table OrderD (order_id, transaction_date, amount)

我正在使用触发器来计算金额。我的问题是它无法获得价格的值(value)。我的代码有什么问题吗？

CREATE DEFINER = CURRENT_USER TRIGGER `mydb`.`OrderD_BEFORE_INSERT`
BEFORE INSERT ON `OrderD`
FOR EACH ROW
  BEGIN
    DECLARE quantity INT;
    DECLARE price FLOAT;

    SET new.transaction_date = NOW(),
    @quantity :=
    (SELECT out_quantity
     FROM Goods_has_Taker
     WHERE Goods_has_Taker.order_id = new.order_id),
    @price :=
    (SELECT price
     FROM Goods
       INNER JOIN Goods_has_Taker
         ON Goods.good_id = Goods_has_Taker.good_id
     WHERE new.order_id = Goods_has_Taker.order_id
    ),
    new.amount = @quantity * @price;
  END

Answer 1

如果您将这行脚本 Where new.order_id = Goods_has_Taker.order_id 更改为 Where Goods_has_Taker.order_id = new.order_id

CREATE DEFINER = CURRENT_USER TRIGGER `mydb`.`OrderD_BEFORE_INSERT` 
       BEFORE INSERT ON `OrderD` FOR EACH ROW
BEGIN
  DECLARE quantity Int;
  declare price FLoat;

  set new.transaction_date=NOW(),
      @quantity := 
        (select out_quantity from Goods_has_Taker 
         where Goods_has_Taker.order_id=new.order_id),
      @price :=
        (select price from  Goods 
         Inner Join Goods_has_Taker 
         On Goods.good_id=Goods_has_Taker.good_id 
         Where Goods_has_Taker.order_id = new.order_id
        ),
      new.amount=@quantity*@price;
END