SCOPE_IDENTITY和@@IDENTITY的用法

SCOPE_IDENTITY和@@IDENTITY的用法SCOPE_IDENTITY和@@IDENTITY的用法

SCOPE_IDENTITY
返回插入到同一作用域中的 IDENTITY 列内的最后一个 IDENTITY 值。一个作用域就是一个模块——存储过程、触发器、函数或批处理。因此，如果两个语句处于同一个存储过程、函数或批处理中，则它们位于相同的作用域中。

语法
SCOPE_IDENTITY( )

返回类型
sql_variant

注释
SCOPE_IDENTITY、IDENT_CURRENT 和 @@IDENTITY 在功能上相似，因为它们都返回插入到 IDENTITY 列中的值。

IDENT_CURRENT 不受作用域和会话的限制，而受限于指定的表。IDENT_CURRENT 返回为任何会话和作用域中的特定表所生成的值。有关更多信息，请参见 IDENT_CURRENT。

SCOPE_IDENTITY 和 @@IDENTITY 返回在当前会话中的任何表内所生成的最后一个标识值。但是，SCOPE_IDENTITY 只返回插入到当前作用域中的值；@@IDENTITY 不受限于特定的作用域。

例如，有两个表 T1 和 T2，在 T1 上定义了一个 INSERT 触发器。当将某行插入 T1 时，触发器被激发，并在 T2 中插入一行。此例说明了两个作用域：一个是在 T1 上的插入，另一个是作为触发器的结果在 T2 上的插入。

假设 T1 和 T2 都有 IDENTITY 列，@@IDENTITY 和 SCOPE_IDENTITY 将在 T1 上的 INSERT 语句的最后返回不同的值。

@@IDENTITY 返回插入到当前会话中任何作用域内的最后一个 IDENTITY 列值，该值是插入 T2 中的值。

SCOPE_IDENTITY() 返回插入 T1 中的 IDENTITY 值，该值是发生在相同作用域中的最后一个 INSERT。如果在作用域中发生插入语句到标识列之前唤醒调用 SCOPE_IDENTITY() 函数，则该函数将返回 NULL 值。

有关说明，请参见示例。

示例
下列示例将创建两个表 TZ 和 TY，并在 TZ 上创建一个 INSERT 触发器。当将某行插入表 TZ 中时，触发器 (Ztrig) 将激发并在 TY 中插入一行。


1 USE tempdb
2 GO
3 CREATE TABLE TZ (
4     Z_id  int IDENTITY(1,1)PRIMARY KEY,
5     Z_name varchar(20) NOT NULL)
6
7 INSERT TZ
8    VALUES ('Lisa')
9 INSERT TZ
10    VALUES ('Mike')
11 INSERT TZ
12    VALUES ('Carla')
13
14 SELECT * FROM TZ
15
16 --Result set: This is how table TZ looks
17 Z_id    Z_name
18 -------------
19 1       Lisa
20 2       Mike
21 3       Carla
22
23 CREATE TABLE TY (
24     Y_id  int IDENTITY(100,5)PRIMARY KEY,
25     Y_name varchar(20) NULL)
26
27 INSERT TY (Y_name)
28    VALUES ('boathouse')
29 INSERT TY (Y_name)
30    VALUES ('rocks')
31 INSERT TY (Y_name)
32    VALUES ('elevator')
33
34 SELECT * FROM TY
35 --Result set: This is how TY looks:
36 Y_id   Y_name
37 ---------------
38 100    boathouse
39 105    rocks
40 110    elevator
41
42 /*Create the trigger that inserts a row in table TY
43 when a row is inserted in table TZ*/
44 CREATE TRIGGER Ztrig
45 ON TZ
46 FOR INSERT AS
47    BEGIN
48    INSERT TY VALUES ('')
49    END
50
51 /*FIRE the trigger and find out what identity values you get
52 with the @@IDENTITY and SCOPE_IDENTITY functions*/
53 INSERT TZ VALUES ('Rosalie')
54
55 SELECT SCOPE_IDENTITY() AS [SCOPE_IDENTITY]
56 GO
57 SELECT   @@IDENTITY AS [@@IDENTITY]
58 GO
59
60 --Here is the result set.
61 SCOPE_IDENTITY
62 4
63 /*SCOPE_IDENTITY returned the last identity value in the same scope, which was the insert on table TZ*/
64
65 @@IDENTITY
66 115
67 /*@@IDENTITY returned the last identity value inserted to TY by the trigger, which fired due to an earlier insert on TZ*/
68
69
请参见
自己写的一段测试代码


1
2 use pubs
3 go
4
5 create table tx
6 (
7      xid int identity(1,1) primary key,
8      xname varchar(50)
9 )
10 go
11
12 insert into tx values ('aaa')
13 insert into tx values ('bbb')
14 insert into tx values ('ccc')
15
16 select * from tx
17
18 create table tz
19 (
20      zid int identity(1,1) primary key,
21      zname varchar(50)
22 )
23 go
24
25 insert into tz values ('AAA')
26 insert into tz values ('BBB')
27 insert into tz values ('CCC')
28 insert into tz values ('DDD')
29 insert into tz values ('EEE')
30 insert into tz values ('FFF')
31
32 select * from tz
33
34 create trigger xztrig on tx
35 after insert
36 as
37     begin
38         insert into tz values ('gongxu')
39     end
40 go
41
42 insert into tx values ('myname')
43
44 SELECT SCOPE_IDENTITY() AS [SCOPE_IDENTITY]
45 GO
46 SELECT   @@IDENTITY AS [@@IDENTITY]
47 GO
48
49 select * from tx
50 select * from tz

返回值为：

SCOPE_IDENTITY
---------------------------------------
4

(1 行受影响)

@@IDENTITY
---------------------------------------
7

(1 行受影响)


下面分别是两个表中的数据:


xid         xname
----------- --------------------------------------------------
1           aaa
2           bbb
3           ccc
4           myname

(4 行受影响)

zid         zname
----------- --------------------------------------------------
1           AAA
2           BBB
3           CCC
4           DDD
5           EEE
6           FFF
7           gongxu

(7 行受影响)