title: MSSQL Trigger - Updating newly inserted record on INSERT
body: <p>I wish to make a modification (Set Deleted = 1) to rows being inserted into my table <code>CustomerContact</code> if the <code>SELECT</code> statement returns more than 0.</p>
  
  <p>I have the following, but it remains untested:</p>
  
  <pre><code>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 &gt;= 1)
  
          UPDATE CustomerContact
          SET Deleted = 1
          WHERE CustomerContactID IN (SELECT CustomerContactID FROM INSERTED);
  
  END
  </code></pre>
  
  <p>Within my <code>IF</code> statement, I am using <code>FROM INSERTED</code>, assuming that this will return the <strong>newly inserted id</strong> for the record that was created by the insert.</p>
  
  <p>I have two questions about this statement:</p>
  
  <ul>
  <li><p>Will this part of the statement perform an <code>UPDATE</code> just the record
  that was just inserted into <code>CustomerContact</code>?</p>
  
  <p><code>UPDATE CustomerContact
             SET Deleted = 1
             WHERE CustomerContactID IN (SELECT CustomerContactID FROM INSERTED);</code></p></li>
  <li><p>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 <code>SELECT</code> statement?</p></li>
  </ul>
  
  <p><code>CustomerContactID</code> is an auto-incrementing primary key column.</p>