Posted by Rico on 04/13/06 22:07

Hmmm,

My mistake. Never believe what you read the first time I guess. I got the
info from an MSDN forum page, but didn't bookmark the page, so I'll have to
find it again. I did find reference to something similar in the MSDN
library which mentions returning the expected Identity value after a trigger
has fired on a table without an identity field. Luckily there are no
triggers on this DB at this point, so that will at least buy me some time
until we can get something mapped out for the client.

Rick

"Lyle Fairfield" <lylefairfield@aim.com> wrote in message
news:Xns97A48FC10EA65lylefairfieldaimcom@216.221.81.119...
> "Rico" <r c o l l e n s @ h e m m i n g w a y . c o mREMOVE THIS PART IN
> CAPS> wrote in news:caw%f.9156$P01.6110@pd7tw3no:
>
>> Hi Tom,
>>
>> Just so you know, triggers and other server side operations will not
>> affect the @@identity result and hence, will not return an incorrect
>> result.
>>
>> Rick
>
> That seems to be the opposite of what this exert from SQL 2005 BOL says.
> I have made two sections UpperCase.
>
> ----------
> "SCOPE_IDENTITY, IDENT_CURRENT, and @@IDENTITY are similar functions
> because they return values that are inserted into identity columns.
>
> IDENT_CURRENT is not limited by scope and session; it is limited to a
> specified table. IDENT_CURRENT returns the value generated for a specific
> table in any session and any scope. For more information, see
> IDENT_CURRENT (Transact-SQL).
>
> SCOPE_IDENTITY and @@IDENTITY return the last identity values that are
> generated in any table in the current session. However, SCOPE_IDENTITY
> returns values inserted only within the current scope; @@IDENTITY is not
> limited to a specific scope.
>
> For example, there are two tables, T1 and T2, and an INSERT trigger is
> defined on T1. WHEN A ROW IS INSERTED TO T1, THE TRIGGER FIRES AND
> INSERTS A ROW IN T2. This scenario illustrates two scopes: the insert on
> T1, and the insert on T2 by the trigger.
>
> Assuming that both T1 and T2 have identity columns, @@IDENTITY and
> SCOPE_IDENTITY will return different values at the end of an INSERT
> statement on T1. @@IDENTITY WILL RETURN THE LAST IDENTITY COLUMN VALUE
> INSERTED ACROSS ANY SCOPE IN THE CURRENT SESSION. THIS IS THE VALUE
> INSERTED IN T2. SCOPE_IDENTITY() will return the IDENTITY value inserted
> in T1. This was the last insert that occurred in the same scope. The
> SCOPE_IDENTITY() function will return the null value if the function is
> invoked before any INSERT statements into an identity column occur in the
> scope.
>
> Failed statements and transactions can change the current identity for a
> table and create gaps in the identity column values. The identity value
> is never rolled back even though the transaction that tried to insert the
> value into the table is not committed. For example, if an INSERT
> statement fails because of an IGNORE_DUP_KEY violation, the current
> identity value for the table is still incremented."
>
> ------------
>
> A session is described as:
>
> By default, a session starts when a user logs in and ends when the user
> logs off. All operations during a session are subject to permission
> checks against that user.
>
>
> --
> Lyle Fairfield

[Back to original message]