There is only one case when @@IDENTITY will not work. That is if your update causes a trigger to fire which also adds a record to a table that has an Identity column.

@@IDENTITY will return the last Identity value created ON A CONNECTION.

I know this is a big issue with SQL Server and I have had to deal with it in many ways.

Once a new record is added, with an identity column as the primarykey, you are going to have to do something special to retrieve this value so you can populate the related child records. Here is what I do in the save method of my data classes

TABLEUPDATE()

If this.newrecord and this.identitykey
*-- get the connection handle of the cursor that was just updated
*-- this is necessary because you MUST query the same connection
lnhandle = CURSORGETPROP("ConnectHandle")
retval = SQLEXEC(lnhandle, "SELECT @@IDENTITY", sqlresult)
SELECT sqlresult
this.primarykey = EVALUATE(field(1))
ENDIF





>Ian, Cetin, John, et al:
>
>Thanks to all of you for your help. I am still having trouble understanding why, if I have a remote view which includes the identity column of the table, when I issue a REQUERY() the idenity column value isn't directly readable from the view cursor.
>
>INSERT INTO tcParent (somecolumns) VALUES (somedatavalues)
>TABLEUPDATE(1,.T.,'tcParent')
>REQUERY('tcParent')
>
>Anytime after the tableupdate() I can suspend, browse the remote view cursor, and see the value I need. But when I do a calculate() Select max() statement it holds a value for many iterations of the loop, increments it and holds it again for some time, and so on....
>
>Given the spec I have, this identity column is the only sure relational identifier I have available.
>
>Thanks!
>Tom.
>
>>>Hi Tom ----
>>>
>>>Ian proposed @@IDENTITY, which is the optimal way of getting the latest ID, but that doesn't always work. Another way would be two open two connections to the table. Save on the first connection, then requery the table with the second connection and get the key that way.
>>>
>>
>>John,
>>
>>How would you know which was the record ou had just added on the second connection? Would you need another key made up from the other fields?
>>
>>In what circumstances does @@IDENTITY not work? - I ask as its been ages now since I was lucky enough to do anything with SQL Server!