Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Strange issue while using IDENTITY values in INSTEAD OF triggers

Found an interesting issue last week....it goes like this: If you are using INSTEAD OF triggers on views (in SQL 2005) and you need to retrieve IDENTITY values you have to be very careful how you do it. It turns out that a simple SELECT from the inserted table can return a NULL for no apparent reason. However, if you use the OUTPUT clause from the same inserted table, it always works! It turns out in my case that I was using both in the trigger and I actually saw both values coming through. So, the takeaway from this post is to always use OUTPUT clause in this situation. This is very counterintuitive and I would consider it a bug. I'm wondering how many of you have run into this? Plz reply and let me know. Thanks, SB

Comments

No comments.

Leave a Comment

Please register or log in to leave a comment.