Re: Identity error?? Please help

From: Marc Durdin (mc-nospam-google_at_durdin.net)
Date: 05/11/04 

Date: 10 May 2004 17:49:20 -0700

"Andre Greyling" <agreyling@optusnet.com.au> wrote in message news:<4085148d$1@newsgroups.borland.com>...
> Thanks for the help - yes the problem is @@IDENTITY related. It is still
> strange how Delphi behaves though. The code is simply:
> (1) qry1.append;
> (2) qry1.fieldbyname('ID').asinteger
>
> qry1 does a simple SELECT on table Master. Master has a trigger (on INSERT)
> which creates detail tables
> each table has an identity column.
> line (2) then returns the identity value of the last detail table - odd !
>
> However, what is the best way to create a master record and retrieve it's
> identity value ? Should the code be in a stored procedure or is there a
> simpler way ?
> Any ideas ?
>
> Bye

It sounds like you've run up against the ADO bug of the year... ADO
uses @@IDENTITY to retrieve the last inserted identity value after a
Post, when it should be using SCOPE_IDENTITY(). There has been much
discussion on whether this issue is a 'bug' as it works as
documented... But if it is not a bug, then it is certainly an
extremely serious design flaw, IMHO. I have seen various workarounds
listed online, none of which really solve the problem:

1. Don't use triggers.

2. Always use stored procedures for INSERTs and UPDATEs.

3. Don't use IDENTITY columns

4. Microsoft recommends: You could implement a business object that
dispenses "identity" column information directly to your application.
This dispenser should be free-threaded, in case multiple applications
are using it, and it probably needs to work with clients across a
network. In this case, a Microsoft Transaction Server implementation
would work the best. (KB article 195910)

5. Use GUIDs in place of IDENTITY

The scary and ugly solution that we are using (because it just
*works*) is to save the @@IDENTITY value on entry to a trigger and
restore it at the end. This is not a new idea - similar solutions
have been used with SQL Server 7.0 in the past to work around the lack
of a SCOPE_IDENTITY() function. For example:

  DECLARE @example TABLE (ii INT IDENTITY (5,23))

  INSERT @example DEFAULT VALUES
  PRINT 'Initial Identity = '+CAST(@@IDENTITY AS VARCHAR)

  -- START OF IDENTITY BACKUP

  DECLARE @BackupIdentitySeederFunc VARCHAR(1000)
  SET @BackupIdentitySeederFunc =
     'DECLARE @BackupIdentity TABLE
         (IdentityID INT IDENTITY('+CAST(@@IDENTITY AS VARCHAR)+', 1))
      INSERT @BackupIdentity DEFAULT VALUES'

  -- END OF IDENTITY BACKUP

  INSERT @example DEFAULT VALUES
  PRINT 'New Identity = '+CAST(@@IDENTITY AS VARCHAR)

  INSERT @example DEFAULT VALUES
  PRINT 'Another New Identity = '+CAST(@@IDENTITY AS VARCHAR)

  -- RETRIEVE ORIGINAL IDENTITY

  EXEC (@BackupIdentitySeederFunc)
  PRINT 'Back to Original Identity = '+CAST(@@IDENTITY AS VARCHAR)

We have ending up with this solution because each of the other options
suggested cause no end of problems for us... and it is a simple patch
to our triggers that should work even if ADO behaviour changes in the
future.

GUIDs would probably be the most flexible solution - but they do have
a substantial performance and size cost - for performance, both in
INSERTs and in SELECTs, as their randomness makes the indexes
inefficient. I found one document that had a workaround for this
performance cost - but I don't know how safe the method described is:
http://www.informit.com/articles/printerfriendly.asp?p=25862.

You would probably also need to use GUIDs from the beginning of a
project, as the changeover cost would be very high.

I hope that one of these ideas might help resolve the issue for you.