Unanswered: Changing of @@IDENTITY item in a trigger
I have a instead of insert trigger that after doing some validation, inserts a record into a specific table. Another table requires the key generated by this insert in order to add a record, which is done at the end of the trigger. When called from a client (via a recordset.update call) the newly created key from the first insert is overridden with the key from the second insert. Any ideas on how I can save the first key, then pass it back to the client? The client currently receives the second key created.
I have exactly the same problem and although I don't have a solution (yet), I have found out that this appears to be by design. ADO does a "Select @@IDENTITY" after an insert to retrieve the key of the record inserted (See Chapter 11 of "Programming ADO" by David Sceppa at http://www.microsoft.com/mspress/boo...chap/3445a.asp). If there was some way to modify ADO's behaviour to use instead "Select IDENT_CURRENT('<tablename>')" then problem solved. The article describes a "Update Resync" dynamic record set property but it doesn't appear to be flexible enough to tell ADO to use IDENT_CURRENT.