title: SQL Server trigger for a specific column value
body: <p>I have a production table called <code>Status</code> with columns shown below including a few sample lines:</p>
  
  <pre><code>Id  TaskOriginator  AssignedTo  Status
  --------------------------------------
  1   CANDYC  EBEKE0  Approved
  2   UPOFA0  CANDYC  Approved
  3   PKOOP0  EBEKE0  Approved
  4   UPOFA0  PKOOP0  Approved
  5   PKOOP0  RVDWE0  Completed
  6   PKOOP0  RVDWE0  Completed
  7   PKOOP0  RVDWE0  Completed
  8   PKOOP0  RVDWE0  Completed
  9   PKOOP0  RVDWE0  Completed
  10  PKOOP0  RVDWE0  Completed
  </code></pre>
  
  <p>Now when a new line gets inserted it gets the status new, then a action happens and the <code>assignedTo</code> column gets updated with a name, now I want to have a trigger on this <code>assignedTo</code> column, if the status is new and the assigned to column gets updated to one of the following person's (e.g. <code>FBOJE0</code>, <code>ASWARTZ</code> or <code>EDEWAAL</code>) then a mail should be send via the trigger.</p>
  
  <p>How will you go about doing a trigger like this, without locking the table for inserts or updates by other processes?</p>