MSSQL 触发器 - 更新插入时新插入的记录

Question

如果

SELECT 语句返回大于 0，CustomerContact我希望对插入到我的表中的行进行修改（Set Delete = 1）。

我有以下内容，但仍未经过测试：

CREATE TRIGGER mark_cust_contact_deleted ON CustomerContact
AFTER INSERT AS
BEGIN
DECLARE @numrows INT;
    /* Determine if order matches criteria for marking customer contact as DELETED immediately */
    SELECT @numrows = COUNT(*)
    FROM [Order] o
    JOIN OrderMeterDetail om
          ON o.OrderID = om.OrderID
    WHERE o.WorkTypeID = 3 AND o.WorkActionID = 26 AND o.WorkStageID IN (109, 309, 409)
    AND om.MeterDetailTypeID = 1 AND om.MeterLocationID IN (2, 4)
    AND o.orderid IN (SELECT OrderID FROM INSERTED);
    /* If the order matches the criteria, mark the customer contact as deleted */
    IF (@numrows >= 1)
        UPDATE CustomerContact
        SET Deleted = 1
        WHERE CustomerContactID IN (SELECT CustomerContactID FROM INSERTED);
END

在我的IF语句中，我正在使用FROM INSERTED，假设这将返回插入创建的记录的新插入的id。

关于这个说法，我有两个问题：

• 语句的这一部分是否仅执行记录UPDATE刚刚插入CustomerContact？

  UPDATE CustomerContact SET Deleted = 1 WHERE CustomerContactID IN (SELECT CustomerContactID FROM INSERTED);

• 根据SELECT语句的结果对刚刚插入的行进行更改是否被认为是正确的方法？

CustomerContactID是自动递增的主键列。

Answer 1

你说"只是插入的记录"。 Inserted可以包含多个记录。如果只有一个，则触发器将按预期运行。但如果不止一个，它就不会。

我会将您的逻辑重写为一个单一的update语句，如下所示......

 Update CustomerContact
 Set Deleted = 1
 From CustomerContact
       inner join inserted on CustomerContact.CustomerContactID = inserted.CustomerContactID
       inner join orders on inserted.OrderID = orders.OrderID
 where
     -- some criteria.

Answer 2

CREATE TRIGGER mark_cust_contact_deleted ON CustomerContact
AFTER INSERT AS
BEGIN
DECLARE @numrows INT;
    /* Determine if order matches criteria for marking customer contact as DELETED immediately */
    -- Get all the records into a temp table 
    SELECT * INTO #Temp
    FROM inserted
    Declare @ID int;
    SELECT @numrows = COUNT(*)
    FROM [Order] o
    JOIN OrderMeterDetail om
          ON o.OrderID = om.OrderID
    WHERE o.WorkTypeID = 3 AND o.WorkActionID = 26 AND o.WorkStageID IN (109, 309, 409)
    AND om.MeterDetailTypeID = 1 AND om.MeterLocationID IN (2, 4)
    AND o.orderid IN (SELECT OrderID FROM #Temp);
 IF (@numrows >= 1)
  BEGIN
    WHILE EXISTS (SELECT TOP 1 * FROM #Temp)
    BEGIN
    SELECT TOP 1 @ID = ID FROM #Temp

    /* If the order matches the criteria, mark the customer contact as deleted */

        UPDATE CustomerContact
        SET Deleted = 1
        WHERE CustomerContactID IN (SELECT CustomerContactID FROM #Temp WHERE ID = @ID);
        DELETE FROM #Temp WHERE ID = @ID
    END
  END   
     DROP TABLE #Temp
END

我认为您可以做这样的事情，调整代码以进一步适应需求，希望这会有所帮助。

Answer 3

这是我用来解决这个问题的最终解决方案：

CREATE TRIGGER mark_cust_contact_deleted ON CustomerContact
AFTER INSERT AS
BEGIN
    UPDATE CustomerContact
    SET Deleted = 1
    FROM CustomerContact cc
        JOIN inserted i
            ON cc.CustomerContactID = i.CustomerContactID
        JOIN [Order] o
            ON i.OrderID = o.OrderID
        JOIN OrderMeterDetail om
            ON i.OrderID = om.OrderID
    WHERE o.WorkTypeID = 3 AND o.WorkActionID = 26 AND o.WorkStageID IN (109, 309, 409)
    AND om.MeterDetailTypeID = 1 AND om.MeterLocationID IN (2, 4)
END