Results 1 to 2 of 2
  1. #1
    Join Date
    Feb 2002

    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.

    Thanks in advance,


  2. #2
    Join Date
    Mar 2003
    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 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.

    The hunt continues...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts