robconery/massive — pull request #201 by hrudham (2013-09-26 12:08:20+00:00)
Title: @@IDENTITY can be hijacked via triggers; replaced with SCOPE_IDENTITY().

Long story short, I was using massive on a development database where a co-worker has set up an audit table via triggers (don't ask). Inserting data resulted in the ID for the audit record coming back, instead of the ID of the item I was attempting to insert.

Phil Haack gives a very clear description of the issue here: http://haacked.com/archive/2005/04/11/beware-of-@@identity-theft-in-sql-server.aspx

The solution is moderately simple: switch out @@IDENTITY for SCOPE_IDENTITY(). Unfortunately this means that I had to stay within the query scope, which resulted in some string concatenation with the INSERT query instead of running a second query, which I'm not 100% comfortable with (hence why I'm glad someone has to read this before accepting the pull request). It does work however.