From
http://foxcentral.net/microsoft/WhatsNewInVFP9_Chapter11.htm"An experienced SQL Server developer might question why @@IDENTITY is used to retrieve the new Autoincrement value rather than SCOPE_IDENTITY(). Usually, SCOPE_IDENTITY() is indeed the better choice because of a limitation of @@IDENTITY. The @@IDENTITY function returns the last Autoincrement value generated on the current connection. If the table you are inserting a record into has a trigger that inserts a record in a second table, and the second table also has an Autoincrement column, @@IDENTITY will return the Autoincrement value from the second table, which is not what you want. The SCOPE_IDENTITY() function returns the last Autoincrement value from the “scope” of your command, which by definition does not include the actions of the trigger. Unfortunately, the SQL Server ODBC driver executes parameterized inserts using the sp_executesql stored procedure. Once this stored procedure is completed, its “scope” is gone, and SCOPE_IDENTITY() returns NULL rather than the Autoincrement value from the insert. Because @@IDENTITY simply returns the last Autoincrement value on the connection, it works in an auto-refresh command."
Examples I use.
This.InsertCmdRefreshCmd = ;
[SELECT PI.ProvID FROM ProvInfo AS PI WHERE PI.ProvID = @@IDENTITY]
This.InsertCmdRefreshCmd = ;
[SELECT PI.ProvID FROM ProvInfo AS PI WHERE PI.ProvID = GETAUTOINCVALUE()]
In the AutoOpen, after the DataSourceType is set.
IF PROPER(This.DataSourceType) = "Native" AND '@@IDENTITY' $ This.InsertCmdRefreshCmd
This.InsertCmdRefreshCmd = STRTRAN(This.InsertCmdRefreshCmd,'@@IDENTITY', 'GETAUTOINCVALUE()')
IF This.DataSourceType = "ODBC" AND 'GETAUTOINCVALUE()' $ This.InsertCmdRefreshCmd
This.InsertCmdRefreshCmd = STRTRAN(This.InsertCmdRefreshCmd,'GETAUTOINCVALUE()', '@@IDENTITY')
>>I don't think you should use IDENT_CURRENT in multi-user application. How can you guarantee it's the last record added by this user?
>It's not failsafe, but quite, as the InsertRefreshCmd is executes right after the insert. And it's better than uising @@IDENTITY.
>
>You are right, in that IDENT_CURRENT() could return an ID from a record inserted afterwards by another user, as it's scoped to the table, not to a connection.
>
>SCOPE_IDENTITY() might be better, especially if you don't share a connection, but if you share connections I'd rather rely on IDENT_CURRENT().
>
>Bye, Olaf.
Charlie