时间戳

首页 > 解决方案 > 如何为此类条件编写触发器？(3张桌子)

sql - 如何为此类条件编写触发器？(3张桌子)

问题描述

我有 3 张桌子。当您将数据添加到 Production 表时，该产品所需的成分数量将从 RawMaterialsWarehouse 表中减去。成分表显示了所消耗产品的 ID。

原料应减去配料表中规定的数量乘以生产数量

CREATE TABLE RawMaterialsWarehouse (
ID int primary key identity(1,1) not null,
Name varchar(30) not null unique,
amountperunit float not null,
quantity float not null
);

CREATE TABLE Production (
ID int primary key identity(1,1) not null,
ProductsID int foreign key (Products ID) references FinishedProductsWarehouse (ID) not null,
Quantity float not null,
Date date not null
);

CREATE TABLE Ingredients (
ID int primary key identity(1,1) not null,
ProductsID int foreign key (ProductsID) references FinishedProductsWarehouse (ID) not null,
RawMaterialsID int foreign key (RawMaterialsID) references RawMaterialsWarehouse (ID) not null,
RawMaterialQuantity float not null
);

简要地：

生产：这是产品编号及其数量。

成分：这里是消耗原材料的产品ID；原材料编号；以及每单位产品的原材料量。

RawMaterialsWarehouse：这里是原材料的总量。

当生产 + ：为该产品指定的所有原材料都从 RawMaterialsWarehouse 中扣除。

标签： sqlsql-serverdatabase

解决方案

CREATE TRIGGER Trg_Production ON Production
AFTER INSERT, UPDATE
AS

SET NOCOUNT, XACT_ABORT ON;   -- best practice to use these two

UPDATE rm
SET quantity = rm.quantity - i.Quantity
FROM
    (SELECT i.ProductsID, Quantity = i.Quantity - ISNULL(d.Quantity, 0)
    FROM inserted i
    LEFT JOIN deleted d ON d.ID = I.ID  -- for updates we take away the difference
) i
JOIN Ingredients ing ON ing.ProductsID = i.ProductsID
JOIN RawMaterialsWarehouse rm ON rm.ID = ing.RawMaterialsID
WHERE i.Quantity > 0;   --filter out zero updates

GO

推荐阅读