代码之家 › 专栏 › 技术社区 › Charles

基于比较删除值和插入值的SQL触发器操作

triggers sql

Charles · 技术社区 · 4 年前

我用C#编写了一个库存应用程序,想跟踪库存数量的变化。为此,我有两个表:tblInventory和tblInvChange。目前,我有一个AFTER UPDATE触发器,通过在tblInvChange中添加以下内容可以很好地工作:(使用d.表示删除,I.表示插入)d.lastphysical;i.最后物理;d.数量;i.数量。

我的问题是,如果在同一天,我回去更改一个项目的数量,我会在同一个项目上得到同一天的两条记录。如果日期不存在,我想让触发器在tblInvChange表中插入一条记录,如果存在,则更新当前记录。

    BEGIN
If(Select [fldlastPhysical] from deleted) <> (Select [fldLastPhysical] from inserted)

INSERT tblInvTracking(keyProductID, fldLocationId, fldLastPhysical, fldQuantity, fldInventoryChange, fldNewQuantity)
SELECT
   D.keyProductID, D.fldLocationID, D.fldLastPhysical, d.fldQuantity, i.fldLastPhysical, i.fldQuantity
FROM
   DELETED D JOIN INSERTED I ON D.keyProductID = I.keyProductID AND D.fldLocationID = I.fldLocationID;

Else

UPDATE tblInvTracking(keyProductID, fldLocationId, fldLastPhysical, fldQuantity, fldInventoryChange, fldNewQuantity)
SELECT
   D.keyProductID, D.fldLocationID, D.fldLastPhysical, d.fldQuantity, i.fldLastPhysical, i.fldQuantity
FROM
   DELETED D JOIN INSERTED I ON D.keyProductID = I.keyProductID AND D.fldLocationID = I.fldLocationID;

END

这是我的理解,但不起作用。如果能提供一个正确实现这一点的例子,我们将不胜感激。

0 回复 | 直到 4 年前

Dale K 4 年前

您需要创建2条语句,一条插入不存在的记录,一条更新存在的记录。据我所知,情况如下,但这不是100%正确的,你需要理清逻辑,确保它与你想要实现的目标相匹配。

注:这是假设 fldLastPhysical 是感兴趣的日期,并且它是 date 不是a datetime .

-- WHERE THE RECORD DOESN'T EXIST FOR THE GIVEN DATE - ADD A NEW ONE
INSERT tblInvTracking(keyProductID, fldLocationId, fldLastPhysical, fldQuantity, fldInventoryChange, fldNewQuantity)
    SELECT D.keyProductID, D.fldLocationID, D.fldLastPhysical, d.fldQuantity, i.fldLastPhysical, i.fldQuantity
    FROM DELETED D
    JOIN INSERTED I ON D.keyProductID = I.keyProductID AND D.fldLocationID = I.fldLocationID
    -- ONLY ADD A NEW RECORD FOR THIS DATE IF IT DOESN"T ALREADY EXIST
    WHERE NOT EXISTS (
      SELECT 1
      FROM tblInvTracking T
      WHERE T.fldlastPhysical = D.fldLastPhysical
      AND T.keyProductID = D.keyProductID AND T.fldLocationID = D.fldLocationID
    );

-- WHERE THE RECORD EXISTS FOR THE GIVEN DATE - UPDATE EXISTING
UPDATE T SET
    fldQuantity = T.fldQuantity + X.fldQuantity
    -- It appears the following line is a datetime column, so you will need to determine what logic is required there - maybe just a straight update.
    , fldInventoryChange = X.fldInventoryChange
    , fldNewQuantity = T.fldNewQuantity + X.fldNewQuantity
FROM tblInvTracking T
INNER JOIN (
    SELECT D.keyProductID, D.fldLocationID, D.fldLastPhysical, d.fldQuantity, i.fldLastPhysical fldInventoryChange, i.fldQuantity fldNewQuantity
    FROM DELETED D
    JOIN INSERTED I ON D.keyProductID = I.keyProductID AND D.fldLocationID = I.fldLocationID
    -- ONLY ADD A NEW RECORD FOR THIS DATE IF IT DOESN"T ALREADY EXIST
    WHERE EXISTS (
      SELECT 1
      FROM tblInvTracking T
      WHERE T.fldlastPhysical = D.fldLastPhysical
      AND T.keyProductID = D.keyProductID AND T.fldLocationID = D.fldLocationID
    )
) X ON T.fldlastPhysical = X.fldLastPhysical AND T.keyProductID = X.keyProductID AND T.fldLocationID = X.fldLocationID