@@identity

I have a problem hopefully someone can help with...
I have a stored procedure which ...
Writes to table A (table A has an identity column)
Gets the identity column using @.@.IDENTITY (OR @.@.SCOPE_IDENTITY)
Updates a table; setting column X to the IDENTITY value just retrieved
Table A has a couple of triggers on it, one of which writes to a different
table - and that table has an identity column.
Using either @.@.IDENTITY or @.@.SCOPE_IDENTITY I cannot get it to return the
identiy column for the table I wrote to (TABLE A), it returns the identity
value of the insert inside the triggers.
Any suggestions on how I get the correct value after writing to table A?
Hope this makes some sense!
Thanks> Using either @.@.IDENTITY or @.@.SCOPE_IDENTITY I cannot get it to return the
> identiy column for the table I wrote to (TABLE A), it returns the identity
> value of the insert inside the triggers.
There is a pretty good explanation in the BOL about how it works
If you insert a value into TableA by using @.@.scope_identity() function you
will get the last values of the identity column of the TableA
"..." <...@.nowhere.com> wrote in message
news:emnME2LAGHA.3496@.TK2MSFTNGP11.phx.gbl...
>I have a problem hopefully someone can help with...
> I have a stored procedure which ...
> Writes to table A (table A has an identity column)
> Gets the identity column using @.@.IDENTITY (OR @.@.SCOPE_IDENTITY)
> Updates a table; setting column X to the IDENTITY value just retrieved
> Table A has a couple of triggers on it, one of which writes to a different
> table - and that table has an identity column.
> Using either @.@.IDENTITY or @.@.SCOPE_IDENTITY I cannot get it to return the
> identiy column for the table I wrote to (TABLE A), it returns the identity
> value of the insert inside the triggers.
> Any suggestions on how I get the correct value after writing to table A?
> Hope this makes some sense!
> Thanks
>
>|||.... wrote:

> I have a problem hopefully someone can help with...
> I have a stored procedure which ...
> Writes to table A (table A has an identity column)
> Gets the identity column using @.@.IDENTITY (OR @.@.SCOPE_IDENTITY)
> Updates a table; setting column X to the IDENTITY value just retrieved
> Table A has a couple of triggers on it, one of which writes to a different
> table - and that table has an identity column.
> Using either @.@.IDENTITY or @.@.SCOPE_IDENTITY I cannot get it to return the
> identiy column for the table I wrote to (TABLE A), it returns the identity
> value of the insert inside the triggers.
> Any suggestions on how I get the correct value after writing to table A?
> Hope this makes some sense!
> Thanks
If you reference SCOPE_IDENTITY() in the proc then it will return the
last IDENTITY value inserted in the current scope (not in the trigger).
If you reference @.@.IDENTITY it will return the IDENTITY value from ANY
scope (including the trigger).
To reference the TableA IDENTITY value in the trigger itself don't use
either of those functions. Refer to the INSERTED virtual table instead.
Your trigger code should accommodate the fact that there may be more
than one row inserted.
There is no such function as @.@.SCOPE_IDENTITY.
David Portas
SQL Server MVP
--|||SCOPE_IDENTITY should work for you
If you want the latest identity regardless of scope and session then
take a look at IDENT_CURRENT
IDENT_CURRENT returns the last identity value generated for a specific
table in any session and any scope.
You would use it like this: SELECT IDENT_CURRENT('TableA') --TableA
would be your table name
http://sqlservercode.blogspot.com/|||not sure what @.@.scope_identity is -- SCOPE_IDENTITY() is the correct
function.
@.@.IDENTITY should have the last inserted identity value (the one
inserted into from the trigger)
SCOPE_IDENTITY() has last inserted identity from your session, so when
you check it, it should be as soon after the related insert as possible.
If this still doesn't help, could you post the proc and trigger code?
This will help posters give better answers.
... wrote:
> I have a problem hopefully someone can help with...
> I have a stored procedure which ...
> Writes to table A (table A has an identity column)
> Gets the identity column using @.@.IDENTITY (OR @.@.SCOPE_IDENTITY)
> Updates a table; setting column X to the IDENTITY value just retrieved
> Table A has a couple of triggers on it, one of which writes to a different
> table - and that table has an identity column.
> Using either @.@.IDENTITY or @.@.SCOPE_IDENTITY I cannot get it to return the
> identiy column for the table I wrote to (TABLE A), it returns the identity
> value of the insert inside the triggers.
> Any suggestions on how I get the correct value after writing to table A?
> Hope this makes some sense!
> Thanks
>
>