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

    Unanswered: Changing of @@IDENTITY item in a trigger

    Hello!

    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,

    Everett
    Everett

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

    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
  •