Post reply

@@IDENTITY

  • October 28, 2003 at 8:48 am

    #82819

    OK, this is really baffling me.

    We have a stored procedure that performs the following scenario:

    INSERT INTO tblA (fld1, fld2) VALUES (@val1, @val2)

    SELECT @idField = @@IDENTITY

    tblA has an INSERT trigger that inserts a record into another table. The problem lies in the fact that now the stored proceure returns the @@IDENTITY value of the insert performed by the insert trigger, not the insert performed by the stored procedure. Does anyone know a way around this? I can't think of any work arounds.

    Shawn

  • October 28, 2003 at 9:44 am

    #479912

    Try using SCOPE_IDENTITY() as opposed to @@IDENTITY.

    @@IDENTITY returns the most recently assigned identity value across the entire database (you're triggered INSERT, for example), whereas SCOPE_IDENTITY() will return the most recently assigned identity value within the context of your procedure / statement, etc.

    See Books Online for a more eloquent explanation.

  • October 28, 2003 at 11:10 am

    #479939

    Thanks so much for the reply. Seems to work great. I had never even heard of SCOPE_IDENTITY.. Am now changing all of my stored procedures that use @@IDENTITY over to SCOPE_IDENTITY so triggers will not cause issues in the future.

    Thanks again!

    Shawn

  • October 29, 2003 at 6:49 am

    #480090

    Learn something new everyday....

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply