java - 通过MySQL中的发票表更新库存表

Question

我有三个表，分别是customer、stock、invoice。

customer table PK is CNo(Customer No)
stock table PK is PNo(Product NO)

发票表获取 PK(来自 customer 表的 CNo 和来自 stock 表的 PNo)作为其关联键。

我想要做的是，通过invoice表更新库存表QuntyAvailable列。

例如。当客户从 PNo 2(即 Dettol)获得 2 个数量时，它会自动将上述产品所在行的库存表 QuntyAvailable 列更新为 18( QuntyAvailable-Qty)。

我多次尝试各种查询，但只得到错误。比如PK不能更新..啦啦啦……

请帮我解决这个问题，谢谢。

customer table

CNo(PK)   | Name        | Address
1         | Jhon        | 23, Hill St, NY.
2         | Sam         | 24, Bejin , Chaina.
3         | Nic         | 25, London ,England.


stock table

PNo(PK)  | Description   | Each Price  | QntyAvailable
1        | Dettol        | $2          |   10
2        | Astra         | $5          |   20


invoice table

CNo(PK)    | PNo(PK)    | Qty      | value
1          | 2          | 2        | $10
2          | 1          | 3        | $6        

更新完成后，我想要这样的库存表...

 stock table

PNo(PK)  | Description   | Each Price  | QntyAvailable
1        | Dettol        | $2          |   7
2        | Astra         | $5          |   18

请帮助我..我正在使用 mysql 服务器和 netbeans IDE

我的查询-------------

s.executeUpdate("INSERT INTO 发票(CNo,PNo,Qty,Value) VALUES('1','2','10','150')"); s.executeUpdate("更新库存集 QuntyAvailable=QuntyAvailable-10 WHERE Pno ='2'");

Answer 1

如果您想在发票中插入时更新库存表，您可以在插入后使用插入的数据进行更新(就像您尝试的那样，唯一看起来错误的是='2' 看起来应该只是 =2)或在触发器中执行此操作，让新插入的值提供更新:

CREATE TRIGGER updateStock AFTER INSERT ON invoice
  FOR EACH ROW BEGIN
      UPDATE stock set QntyAvailable=QntyAvailable- new.Qty WHERE Pno =new.Pno;
  END;
//

您可以查看this fiddle看看它的工作原理。

附注

您确实应该有一个 InvoiceID。按照您的构建方式，您不允许客户两次购买同一产品。

附注2 - 创建触发器时出现的错误与未设置 DELIMITER 有关。如果你不设置它，语句将在第一个结束；您需要在触发器之前设置它并用它结束触发器定义。之后，您可以将分隔符设置回;。

DELIMITER //
CREATE TRIGGER updateStock AFTER INSERT ON invoice
  FOR EACH ROW BEGIN
      UPDATE stock set QntyAvailable=QntyAvailable- new.Qty WHERE Pno =new.Pno;
  END;
//
DELIMITER ;