Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2004
    Location
    London
    Posts
    21

    Unanswered: @@Identity being over-written by Insert Trigger in stored procedure.

    Hi All

    I have a problem with an existing stored procedure that is used to insert a new entry to a table (using an Insert statement).

    I have been using the @@Identity global variable to return the identity column (id column) back to the calling routine. This has worked fine for years until recently an ‘after insert Trigger’ has been added to the table being updated.

    Now the @@Identity is returning the identity value of the trigger that was called instead of the original table insert.

    Does anyone know how I can code around this issue (without using a select statement within my stored proc, as these have been known to cause locks in the past).

    Thank in advance.

    Eamon.

  2. #2
    Join Date
    Dec 2002
    Posts
    1,245
    Look at SCOPE_IDENTITY and/or IDENT_CURRENT

    Regards,

    hmscott
    Have you hugged your backup today?

  3. #3
    Join Date
    Oct 2004
    Location
    London
    Posts
    21

    Smile

    Thank you very much hmscott. SCOPE_IDENTITY works a treat!!!

Posting Permissions

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