sql-server - MSSQL 触发器 - 在 INSERT 上更新新插入的记录

Question

如果 SELECT 语句返回大于 0，我希望对插入到我的表 CustomerContact 中的行进行修改(Set 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，假设这将为创建的记录返回 newly inserted id通过插入。

我对这个声明有两个问题:

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

  更新客户联系方式
SET 删除 = 1
WHERE CustomerContactID IN(从 INSERTED 中选择 CustomerContactID)；

• 根据 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.