SQL Sever中使用@@identity的问题

SQL Server中的 @@IDENTITY 是获取数据表中最后一条插入数据的IDENTITY值。比如，表 A 中有个 ID 为自增1的字段，假设此时 ID 的值为100，现在如果我往表A插入一条数据，并在插入后 SELECT @@IDENTITY，则其返回 101，最后一条IDENTITY域（即ID域）的值。

现在问题来了， @@IDENTITY 它总是获取最后一条变更数据的自增字段的值，而忽略了进行变更操作所在的范围约束。比如，我有表 A 和表 B 两个表，现在我在表 A 上定义了一个Insert触发器，当在表 A 中插入一条数据时，自动在表 B 也插入一条数据。此时，大家注意，有两个原子操作：在A中插入一条数据，接着在B中随后插入一条数据。

现在我们想下，假设上面表 A 和表 B 都有IDENTITY自增域，那么我们在表 A 插入一条数据后，使用了 SELECT @@IDENTITY 输出时，输出的到底是 A 还是 B 的自增域的值呢？  答案很明显，是谁最后插入就输出谁，那么就是 B 了。于是，我本意是想得到 A 的自增域值，结果得到了 B 的自增域值，一只 BUG 随之诞生，搞不好还会影响到整个系统数据的混乱。

对于这种情况，可以考虑使用SCOPE_IDENTITY()函数；SCOPE_IDENTITY() 也是得到最后一条自增域的值，但是它是仅限在一个操作范围之内，SCOPE_IDENTITY ()只返回插入到当前作用域中的值，而不像 @@IDENTITY 是取全局操作的最后一步操作所产生的自增域的值的。

（注：在SQL Server中，@@identity、SCOPE_IDENTITY、IDENT_CURRENT 和 @@IDENTITY 在功能上相似，因为它们都返回插入到 IDENTITY 列中的值。 IDENT_CURRENT 不受作用域和会话的限制，而受限于指定的表。IDENT_CURRENT 返回为任何会话和作用域中的特定表所生成的值。）

看下面的例子：

USE tempdb GO 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

输出结果

Z_id	Z_name
1	Lisa
2	Mike
3	Carla

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 ('elevator')   SELECT * FROM TY

输出结果

Y_id	Y_name
100	boathouse
105	rocks
110	elevator

 创建触发器

CREATE TRIGGER Ztrig ON TZ FOR INSERT AS BEGIN INSERT TY VALUES ('') END

 

INSERT TZ VALUES ('Rosalie') SELECT SCOPE_IDENTITY() AS [SCOPE_IDENTITY] GO SELECT @@IDENTITY AS [@@IDENTITY] GO

输出结果

--Here is the result set. SCOPE_IDENTITY 4 /*SCOPE_IDENTITY returned the last identity value in the same scope, which was the insert on table TZ*/   @@IDENTITY 115 /*@@IDENTITY returned the last identity value inserted to TY by the trigger, which fired due to an earlier insert on*/