sql - How do I write a trigger for such conditions? (3 tables)

Question

I have 3 tables. When you add data to the Production table, the quantity of ingredients that are needed for this product is subtracted from the RawMaterialsWarehouse table. The Ingredients table shows the ID of the product for which it is consumed.

And the raw materials should be subtracted from the quantity specified in the Ingredients table multiplied by the production quantity

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
);

Briefly:

Production: here is the product id and its quantity.

Ingredients: here is the product id for which the raw material is consumed; the raw material id; and the amount of raw material per unit of product.

RawMaterialsWarehouse: here is the total quantity of raw materials.

When Production + : all raw materials specified for this product are deducted from RawMaterialsWarehouse.

Answer 1

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