SCOPE_IDENTITY and @ identity

SCOPE_IDENTITY, IDENT_CURRENT, and @ IDENTITY are similar functions, because they all return values inserted into the ID column.

IDENT_CURRENT is not restricted by the scope and session, but by the specified table. IDENT_CURRENT returns the value generated for a specific table in any session and scope. When calling it, you must provide the unique parameter that represents the table name. You can get the last IDENTITY value of any table you want, even if your code does not have an insert action. Example: IDENT_CURRENT ('t1 ');

SCOPE_IDENTITY and @ IDENTITY return the last id value generated in any table in the current session. However, SCOPE_IDENTITY only returns the value inserted into the current scope; @ IDENTITY is not limited to a specific scope.

For example, there are two tables T1 and T2, And the INSERT trigger is defined on T1. When a row is inserted to T1, the trigger is triggered and a row is inserted to T2. This solution demonstrates two scopes: insert on T1 and insert through trigger on T2.

If both T1 and T2 have an ID column, @ IDENTITY and SCOPE_IDENTITY return different values at the end of the INSERT Statement on T1. @ IDENTITY returns the value of the last identifier column inserted in any scope of the current session. This is the value inserted in T2. SCOPE_IDENTITY () returns the IDENTITY value inserted in T1. This is the last insert in the same scope. If the SCOPE_IDENTITY () function is called before any INSERT statement acts on the identifier column in the scope, the function returns a null value.

Scope_identy () current session, current scope
@ Identity current session, all scopes

--------------- The program is as follows -----------------------
Create table tz (
Z_id int IDENTITY (1, 1) primary key,
Z_name varchar (20) not null)
INSERT TZ
VALUES ('lisa ')
INSERT TZ
VALUES ('Mike ')
INSERT TZ
VALUES ('carla ')
SELECT * FROM TZ

Go
Create table ty (
Y_id int IDENTITY (100,5) primary key,
Y_name varchar (20) NULL)
Insert ty (Y_name)
VALUES ('boathouse ')
Insert ty (Y_name)
VALUES ('rocks ')
Insert ty (Y_name)
VALUES ('evator ')
SELECT * FROM TY

Go

Create TRIGGER Ztrig
ON TZ
After INSERT
BEGIN
Insert ty values ('')
END

Go

Insert tz values ('rosalie ')
SELECT SCOPE_IDENTITY () AS [SCOPE_IDENTITY] scope: TZ table
SELECT @ identity as [@ IDENTITY] scope: TY table

------------ Result --------------
4

115

 

 

Go to: http://www.cnblogs.com/deymmtd/archive/2009/02/25/1397653.html