"Andre Greyling" <
XXXX@XXXXX.COM>writes news:<4085148d$
XXXX@XXXXX.COM>...
Quote
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:
www.informit.com/articles/printerfriendly.asp
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.
