Posts
463
Comments
322
Trackbacks
1
T-SQL: Triggers and Dirty Reads

Someone was troubleshooting a production issue and asked me “If a trigger updates the row that is being inserted, is it possible to see the row before the trigger completes its task?”  Since I didn't know, and couldn't find a clear answer on Google (I hate it when that happens), I tested it out.  I created a test table with an identity column and a value column.  Then I created a trigger that waited for one minute when fired (using WAITFOR '00:01:00'), and then incremented the inserted value.

Sure enough, when trying to do a normal select right after the insert happens, it is blocked.  But, if you add the with (nolock) hint to the select statement, you can see the original insert.  So, it behaves as one would expect in situations where the isolation level is set to READ UNCOMMITTED.

 

posted on Thursday, July 21, 2005 12:25 PM
Comments
No comments posted yet.

Post Comment

Title *
Name *
Email
Url
Comment *  
Please add 6 and 7 and type the answer here: