我需要帮助编写Server触发器
我需要帮助编写Server触发器
提问于 2016-07-01 00:25:58
我做了简化的实体模型来显示与我的问题相关的表格
我需要一个AFTER INSERT触发器Packings。
我希望它能更新对应的dRoastedStocks表行中的数量，用于INSERTED行Packings中所选的混合咖啡中的每一种咖啡种类。
数量应按以下方式计算：
"NewOne" = "OldOne" - "Capacity of selected package" * "Ratio of CoffeeSort in selected Mix" * "Number of packages"在这里，我已经使用实体框架在C#中实现了它，它工作得很好，如果有人能将它变成Server触发器，我将非常感激
(NewPacking指的是添加的行)
//For each CoffeeSort included into selected Mix
foreach (var mixDetail in NewPacking.Mix.Mix_Details)
{
// Find row in dRoastedStocks which refers to CoffeeSort
var roastedStock = _context.dRoastedStocks.First(x => x.CoffeeSort.Id == mixDetail.CoffeeSort.Id);
// Update it's quantity with new value by substracting packed quantity
// Packed quantity is calculated as "Capacity of selected package" * "Ratio of CoffeeSort in selected Mix" * "Number of packages"
roastedStock.Quantity -= NewPacking.PackQuantity * mixDetail.Ratio/100 * NewPacking.Package.Capacity;
}解释起来很复杂，所以我希望这个计划能有所帮助。
编辑:下面是我尝试用SQL编写的内容，它与我之前编写的C#几乎相同
CREATE TRIGGER RoastedStocks_Insert_Packing
ON Packings
AFTER INSERT
AS
UPDATE dRoastedStocks
SET dbo.dRoastedStocks.Quantity =
dbo.dRoastedStocks.Quantity -
(SELECT Ratio
FROM Mix_Details
WHERE Mix_Id = (SELECT INSERTED.Mix_Id FROM INSERTED)
AND Mix_Details.CoffeeSort_Id = dRoastedStocks.CoffeeSort_Id) / 100
* (SELECT INSERTED.PackQuantity FROM INSERTED)
* (SELECT Capacity FROM Packages WHERE Id = (SELECT INSERTED.Package_Id FROM INSERTED))回答 1
Stack Overflow用户
回答已采纳
发布于 2016-07-01 05:10:01
正如这里发布的许多触发器一样，您也不知道一次插入许多行的情况。如果出现"scalar...but多行返回“错误，所有这些子查询都会失败。
请看一下这种方法：
UPDATE rs SET
Quantity -= md.Ratio / 100 * i.PackQuantity * pk.Capacity
FROM INSERTED i
INNER JOIN dbo.Mix_Details md
ON md.Mix_Id = i.Mix_Id
INNER JOIN dbo.Packages pk
ON pk.Id = i.Package_Id
INNER JOIN dbo.dRoastedStocks rs
ON rs.CoffeeSort_Id = md.CoffeeSort_Id页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/38135249
复制相关文章
相似问题
腾讯云开发者
