需要帮助创建触发器以更新不同表中的值 [英] Need help creating trigger to update vales in different tables

问题描述

所以，我有一个关系数据库，我试图创建基本上更新其他字段的触发器..



提供更多信息。我有一个大型数据库，但只提供q几个表，因为其他字段只是员工表等，而且是不必要的。



表格包括订单和股票..



订单（OrderID，ProductID，ItemQuantity）

股票（StockID，ProductID，StockQuantity）



尽量不要专注于挑选我的数据库，我只是想创建一个有效的触发器。



我基本上想要创建通过使订单表中的ItemQuantity远离StockQuantity，从Stock表中更新StockQuantity的触发器。我该怎么做呢？它会从基于产品ID的库存水平中拿走数量。所以基本上就像......



Trigger = StockQuantity = StockQuantity - ItemQuantity where Stock中的ProductID与Order中的ProductID相同。

So, ive got a relational database, and im trying to create triggers that basically update other fields..

To provide more information. I have a big database, but shall only provide q couple of tables as the other fields are just employee tables etc and are unnecessary.

The tables ill include are Order and Stock..

Order (OrderID, ProductID, ItemQuantity)
Stock (StockID, ProductID, StockQuantity)

Try not to focus on nit picking my database, i just want to create a working functioned trigger.

I basically want to create a trigger than updates the StockQuantity from the Stock table by taking the ItemQuantity in the order table away from StockQuantity. How do i go about doing this? It will take away the quantity from the stock level that will be based on the productID.. So essentially something like...

Trigger = StockQuantity = StockQuantity - ItemQuantity where ProductID in Stock is same as ProductID in Order.

推荐答案

您只需要在触发器中进行连接。



这样的事情应该有效：

You just need to do a join in your trigger.

Something like this should work:

UPDATE Stock
SET StockQuantity = StockQuantity - o.ItemQuantity
FROM Stock s
INNER JOIN inserted i ON s.ProductID = i.ProductID
INNER JOIN Order o ON s.ProductID = o.ProductID

但我的猜测是你有很多订单，所以你可能需要做一些像

But my guess is you have lots of orders so you may have to do something like

UPDATE Stock
SET StockQuantity = StockQuantity - SUM(o.ItemQuantity)
FROM Stock s
INNER JOIN inserted i ON s.ProductID = i.ProductID
INNER JOIN Order o ON s.ProductID = o.ProductID

Inner加入触发器中的inserted表，以便只更新那些正在更改的记录。或者，删除插入以执行整个表格。

Inner Join to the inserted table in your trigger so that only those records that are being changed will be updated. Or, remove inserted to do the entire table.

这篇关于需要帮助创建触发器以更新不同表中的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！