You can use scope_identity: Ident_Current to return the identifier value of the specific table in any session and any scope in any scope. @@ iDENTITY Returns the final generated identity value of any table in all scopes of the current session. Scope_identity returns the identifier value that is finally generated for any table in the current session and the current scope.
The following is the SQL online help
Scope_identity returns the last Identity value inserted into the Identity column in the same scope. A scope is a module-stored procedure, trigger, function, or batch. Therefore, if the two statements are in the same stored procedure, function, or batch, they are located in the same scope.
Syntax scope_identity ()
Return Type SQL_VARIANT
Note Scope_identity, Ident_current and @@ identity are functionally similar because they all returns the value inserted into the Identity column.
Ident_current is not limited by the limitations and sessions, and is limited to the specified table. Ident_current returns the value generated by a particular table in any session and scope. For more information, see Ident_current.
Scope_identity and @@ identity returns the last identifier value generated in any table in the current session. However, scope_identity only returns a value inserted into the current scope; @@ identity is not limited to a specific scope.
For example, there are two tables T1 and T2 that define an INSERT trigger on T1. When a row is inserted into T1, the trigger is excited and inserted in T2. This example illustrates two scopes: one is inserted on T1 and the other is insertion of the result of the trigger in T2.
Suppose T1 and T2 have an Identity column, @@ identity and scope_identity will return different values for the last return of the INSERT statement on T1.
@@ identity returns the last Identity column value inserted into any scope in the current session, which is the value inserted in T2.
Scope_identity () returns the Identity value inserted in T1, which is the last INSERT that occurs in the same scope. If you call the Scope_identity () function before the insertion statement is inserted to the identity column, the function will return a null value.
See example.
Example The following example will create two tables and TY and create an INSERT trigger on TZ. When a row is inserted into the table Tz, the trigger (ztrig) will be excited and inserted into the TY.
Use tempdbgocreate table tz (z_id int identity (1, 1) Primary key, z_name varchar (20) not null
INSERT TZ VALUES ('LISA') Insert Tz Values ('Carla')
Select * from tz
--Result set: this is how table tz locksz_id z_name ------------- 1 lisa2 mike3 carla
CREATE TABLE TY (Y_ID Int Id IDENTITY (100, 5) Primary Key, Y_NAME VARCHAR (20) NULL)
INSERT TY (Y_NAME) VALUES ('Rocks') insert TY (Y_NAME) VALUES ('Elevator') Select * from Ty - Result Set: this is how tyloks: y_id y_name --------------- 100 Boathouse105 Rocks110 Elevator
/ * CREATE The TRIGGER THATS A ROW IN TEY TY WHEN A ROW IS INSERTED in Table Tz * / CREATE TRIGGER ZTRIGON TZFOR INSERT AS Begin Insert Ty Values (') End
/ * Fire the Trigger and Find Out What Identity Values You Get with the @@ identity and scope_identity functions * / insert tz value '
Select scope_identity () as [scope_identity] gosent @@ identity as [@@ identity] Go
--Here is the result set.scope_identity4 / * scope_identity returned the last Identity Value in The Same Scope, Which Was The Insert on TZ * /
@@ identity115 / * @@ identity returned the last Identity value Insert to Ty by the Trigger, Which Fired Due To An Earlier Insert on Tz * /
