title: SQL Server "AFTER INSERT" trigger doesn't see the just-inserted row
body: <p>Consider this trigger:</p>
  
  <pre><code>ALTER TRIGGER myTrigger 
     ON someTable 
     AFTER INSERT
  AS BEGIN
    DELETE FROM someTable
           WHERE ISNUMERIC(someField) = 1
  END
  </code></pre>
  
  <p>I've got a table, someTable, and I'm trying to prevent people from inserting bad records. For the purpose of this question, a bad record has a field "someField" that is all numeric.</p>
  
  <p>Of course, the right way to do this is NOT with a trigger, but I don't control the source code... just the SQL database. So I can't really prevent the insertion of the bad row, but I can delete it right away, which is good enough for my needs.</p>
  
  <p>The trigger works, with one problem... when it fires, it never seems to delete the just-inserted bad record... it deletes any OLD bad records, but it doesn't delete the just-inserted bad record. So there's often one bad record floating around that isn't deleted until somebody else comes along and does another INSERT.</p>
  
  <p>Is this a problem in my understanding of triggers? Are newly-inserted rows not yet committed while the trigger is running?</p>