MSSQL Trigger - Updating newly inserted record on INSERT

Question

I wish to make a modification (Set Deleted = 1) to rows being inserted into my table CustomerContact if the SELECT statement returns more than 0.

I have the following, but it remains untested:

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

Within my IF statement, I am using FROM INSERTED, assuming that this will return the newly inserted id for the record that was created by the insert.

I have two questions about this statement:

• Will this part of the statement perform an UPDATE just the record that was just inserted into CustomerContact?

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

• Is this the way that would be deemed correct to make a change to a row that has just been inserted based on the result of a SELECT statement?

CustomerContactID is an auto-incrementing primary key column.

Answer 1

You say "Just the record that was inserted". Inserted can contain more than one record. If there is only one, then your trigger will function as you expect. But if there is more than one, it won't.

I would rewrite your logic into a single update statement along the lines of...

 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.