添加数据以从最后一次插入触发 [英] Add data to trigger from the last insert

问题描述

我面临以下情况：

我创建了一个触发器，该触发器在插入到第三张表时做出反应。当我插入任何数据（例如 1 1 2 ）时，应从单元格的存货金额列中减去最后一个数字，该列必须具有 ID产品（如图所示）。但是我怎么才能知道最后添加的是哪一行呢？我以为首先要通过选择来完成，但这似乎是不可能的。现在，我认为可以在游标的帮助下完成此操作，但它似乎并不是最好的选择。

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 subtracted from the Amount of stock column from cell, which has necessary ID Product (as it's shown on picture). But how can I understand which row was the last added? I thought firstly to do it by select, but it seems impossible. And now I think that it's possible to do it with the help of cursor, but it doesn't seem as the best variant. Is there a better variant how can I do it?

这是我的触发器代码，但不幸的是，每次仅从第一个乘积中减去1。

Here's my code of trigger, but it only subtracts 1 from the 1st product each time, unfortunately:

CREATE TRIGGER AmountInsert ON Amount
AFTER INSERT
AS
BEGIN
    UPDATE Product
    SET Amount_On_Stock =
     (SELECT Amount_On_Stock FROM Product
    WHERE ID_Product = 1) - 1
    WHERE ID_Product = 1
END

推荐答案

您需要了解的第一件事是，在SQL Server的触发器中，提供了 插入的伪表和删除的伪表。您可以使用这些表来确定发生了什么更改。

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.

CREATE TRIGGER dbo.AmountInsert ON dbo.Amount
AFTER INSERT
AS
BEGIN
  set 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
  inner join (
    -- We need to group by ID_Product in case the same product appears in the insert multiple times
    select ID_Product, sum(Amount) Amount
    from Inserted
    group by ID_Product
    -- No need to update is net change is zero
    having sum(Amount) <> 0
  ) I
  on I.ID_Product = P.ID_Product;
END

这篇关于添加数据以从最后一次插入触发的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！