|
|
Posted by Lyle Fairfield on 04/13/06 21:08
"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
Navigation:
[Reply to this message]
|