Always use SCOPE_IDENTITY()

As I make changes to an application I'm reminded how ugly @@IDENTITY is.  We're using triggers to add some rudimentary data synchronization to this application.  The application inserts a record, we capture it in the trigger and go put a copy in the new table.  Except that the trigger sets a new value for @@IDENTITY when it fires.  So our quick changes that weren't going to affect anything are now much bigger than we planned.  Rather than doing this all at the database level we now have to open up the application and touch that code too.  Or find a whole different approach.

And yes, stored procedures would be the best solution yet.  If all updates where run through stored procedures we could just modify that code.  But they didn't do that.

Show Comments