Add Data To Trigger From The Last Insert

I am faced with the following situation: I created a trigger which reacts on insert to the third table. When I insert any data (for example 1 1 2), last number should be subtracte

Solution 1:

The first thing you need to understand is that in a trigger in SQL Server you are provided with an inserted pseudo-table and a deleted pseudo-table. You use these tables to determine what changes have occurred.

I think the following trigger accomplishes what you are looking for - the comments explain the logic.

CREATETRIGGER dbo.AmountInsert ON dbo.Amount
AFTER INSERTASBEGINset nocount on;

  update P set-- Adjust the stock level by the amount of the latest insert
    Amount_On_Stock =coalesce(Amount_On_Stock) - I.Amount
  from dbo.Product P
  innerjoin (
    -- We need to group by ID_Product in case the same product appears in the insert multiple timesselect ID_Product, sum(Amount) Amount
    from Inserted
    groupby ID_Product
    -- No need to update is net change is zerohavingsum(Amount) <>0
  ) I
  on I.ID_Product = P.ID_Product;
END