Anthony Greene

Just another site

Archive for the ‘bit by bit, day by day’ Category

@@identity crisis

leave a comment »

I’ve just spent the past hour tracking down an interesting bug in my sql code.  It’s a little embarasing, seeing as how it is purely a product of some of my own bad habits as a developer.
For background, the current project I’m working on is being developped with Whitehill Technologies’ BPI (Business Process Integration) software.  A consequence of using this tool is having no ability to step into any of my code as it executes, a debugging feature I am earnestly missing right now.
To compensate, I’ve littered my database stored procedures and triggers with statements that write into a debug table.  This is helping me monitor and track various complex processes within BPI as it interacts with my database.  BPI is a tremendously sophisticated application, serving much the same role as MS Biztalk.  However, as an application development tool it is a little clunky, so I find myself doing far more procedural code at the database level than I normally would in a traditional application.
When rows are populated into a dbo.individual table, I need to write a corresponding record into a dbo.entity table.  I’ve also just implemented a full database auditing pattern, where all updates to tables are checked to see if the incoming data is different than the existing data, and if so, log the new data, timestamp, and editing userid to an audit table.
As I’m looking through my debug logs, I’m finding many calls to usp_entity_edit with unsually low individual_ids, like 4, 10, 18, 25, etc. when I know the current @@identity of dbo.individual is in the area of 14500, 14501, 14502, etc.
What’s happened?  I’ve added one of my inserts into my debug table (which is regularly truncated) in the audit trigger of dbo.individual.  When I fetch @@identity of individual to write into the corresponding dbo.entity table, I end up retrieving the identity of the debug statement I just inserted, in dbo.individual’s audit trigger, and not dbo.individual’s individual_id identity.
Sigh!  I’m getting too old to make silly mistakes like this.
The resolution is simple.  Instead of using @@identity, it’s a simple matter to use IDENT_CURRENT(‘table_name‘) instead.  I seem to remember learning this a long time ago, and have forgotten somewhere along the way.
The great honor of working in software development is you truly do have the opportunity to learn something new every day.  The unfortunate irony, however, is many of these things end up getting forgotten over time, and oftentimes need to be relearned.

Written by anthonygreene

July 27, 2006 at 10:08 am