mysql - 订单后TRIGGER和选择查询产品列表的问题

Question

这是我的代码示例：

CREATE TABLE Customer(
CustID int not null,
primary key(CustID)
);

CREATE TABLE Products(
ProdID integer not null,
Quantity NUMERIC(3),
PRIMARY KEY(ProdID)
);

CREATE TABLE Orders(
Id INT not null,
Quantity NUMERIC(3),
Date date,
PRIMARY KEY(Id)
);
alter table Orders add OrdFK INT;
alter table Orders add constraint Ord_FK
foreign key (OrdFK) references Customer(CustID);
alter table Orders add ProdIdFK INT;
alter table Orders add constraint ProdId_FK
foreign key (ProdIdFK) references Products(ProdID);

insert into Products(ProdID, Quantity) values(1, 7);

CREATE TRIGGER  QuantityUpdate
AFTER INSERT
   ON Orders FOR EACH ROW
   
BEGIN
UPDATE Products p
SET p.Quantity = p.Quantity - New.Quantity 
WHERE p.ProdID = New.ProdIDFK

END$$
DELIMITER ;

INSERT into Orders(Id, Quantity, Date, ProdIDFK, OrdFK) values(1, 2, '2020/01/27', 1, 1);

SELECT Quantity from Products where ProdID = 1;

然后它显示最初的产品数量，而不改变数量-2。我认为问题出在...

SET p.Quantity = p.Quantity - New.Quantity 
WHERE p.ProdID = New.ProdIDFK 

...但不知道到底出了什么问题：/ 我试图在 New.ProdID 上更改 New.ProdIdFK，但这并没有帮助。在 Orders 表中，ProdIdFK 引用 Products ProdID，OrdFK 引用 Costumers CustID。

标签： mysqltriggers