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

Question

如果SELECT语句返回多于0行，我希望对插入到我的表CustomerContact中的行进行修改（设置Deleted = 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。

关于这个语句，我有两个问题：

这个语句部分是否只会执行对刚刚插入到CustomerContact中的记录进行UPDATE？

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

这是根据SELECT语句结果对刚刚插入的行进行更改的正确方式吗？

CustomerContactID是自动递增的主键列。

- Luke

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.

- podiluska

---

有没有一种方法可以为每一行执行触发器？ - Luke

你能解释一下为什么如果有多行插入，OP的原始尝试不起作用吗？我没有看到它，他在o.orderid IN (SELECT OrderID FROM INSERTED)设置了@numrows，寻找IF (@numrows >= 1)并更新WHERE CustomerContactID IN (SELECT CustomerContactID FROM INSERTED)，那么它会在哪里失败呢？当然，你使用JOIN的语法更美观 ;) - DrCopyPaste

@DrCopyPaste 因为如果一个记录匹配了 where 条件，那么所有的记录都会被标记为已删除，而不仅仅是匹配条件的记录。 - podiluska

@podiluska 非常感谢您的帮助。我已经发布了一个可能的解决方案。如果您能快速查看并告诉我您的想法，我将不胜感激。 :) - Luke

@Coulton，看起来差不多对了，但是如果不知道你的数据结构，我不能确定。我建议从WHERE子句中移除AND o.OrderID = i.CustomerContactID，并将其移到适当的连接位置。 - podiluska

我在你评论之前刚刚完成了这个 :). 非常感谢你的帮助！ - Luke

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

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

- M.Ali

---

感谢您抽出时间查看我的问题！+1 - Luke

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

- Luke