数据库Mysql中的自动更新库存
我需要了解数据库中的一件事.我有产品表及其总库存，如图所示
I need to know about one thing in databases. I have product table with it's total inventory as seen in image
如果有人知道我该怎么做??请分享谢谢
If any one have any idea that how can i do that?? Please Share it Thanks
推荐答案
当提到关系数据库时，您所问的并不是技术意义上的关系".这种关系就像有一个表引用另一个表的键"，例如将一个带有客户地址的表与另一个带有客户订单的表相关联.无论如何，这超出了您所问的范围，所以要回答您的问题，您可以在应用程序代码或触发器中执行此操作.
What you're asking about isn't technically a "relationship" in the technical sense when referring to relational databases. Such relations are things like having a table that refers to the "key" of another table, for instance relating a table with a customer's address to another table with the customer's order. Anyway, that's beyond the scope of what you asked about so to answer your question, you can do that in the application code or a trigger.
触发器是数据库的功能，它在发生 INSERT、UPDATE 或 DELETE 时执行某事.
Triggers are features of the database that does something when an INSERT, UPDATE, or DELETE happens.
这样的事情应该可以通过对表/列名称进行微调来正常工作:
Something like this should work okay with minor adjustments for table/column names:
UPDATE table2 SET inventory = inventory - NEW.qty where id = NEW.id_product;
现在只包含一个 INSERT；您需要为事件更新创建另一个触发器，并且可能还需要以某种方式处理退回的库存或取消的订单，但这可能是您将在应用程序级别而不是在触发器中处理的事情.
Now that only covers an INSERT; you'll want to create another trigger for Event UPDATE and probably somehow handle returned inventory or cancelled orders as well, but that's probably something you'll handle at the application level rather than in a trigger.
相关文章