>I'm doing some tests here and I find a problem.
>If I have two tables T1 and T2, and the table T1 fires a trigger that inserts a new row in T2, when you try to recovery @@identity, you get a wrong number.
>
Hi Rudolfo,
You can't use SELECT @@IDENTITY as an InsertCmdRefreshCmd in this scenario because it returns the last IDENTITY value generated for the connection. You can't use SELECT SCOPE_IDENTITY() either because ODBC driver executes parameterized command by means of sp_executesql procedure, which changes the scope. I believe the best way to deal with this scenario is to use stored procedure call for the InsertCmd and return IDENTITY value using an OUTPUT parameter or return value. See example in message #
1007757.
Aleksey.