SELECT @@IDENTITY; /* 针对当前会话,所有作用域 Returns the value 100. This was inserted by the trigger.*/ SELECT SCOPE_IDENTITY(); /* 针对当前会话,当前作用域 Returns the value 1. This was inserted by the INSERT statement two statements before this query.*/ SELECT IDENT_CURRENT('t7'); /* 针对全局会话 Returns value inserted into t7, that is in the trigger.*/ SELECT IDENT_CURRENT('t6'); /* 针对全局会话 Returns value inserted into t6. This was the INSERT statement four statements before this query.*/
其实自己试试就明白了,完整的代码如下:
1 USE AdventureWorks2008R2; 2 GO 3 IF OBJECT_ID(N't6', N'U') IS NOT NULL 4 DROP TABLE t6; 5 GO 6 IF OBJECT_ID(N't7', N'U') IS NOT NULL 7 DROP TABLE t7; 8 GO 9 CREATE TABLE t6(id int IDENTITY); 10 CREATE TABLE t7(id int IDENTITY(100,1)); 11 GO 12 CREATE TRIGGER t6ins ON t6 FOR INSERT 13 AS 14 BEGIN 15 INSERT t7 DEFAULT VALUES 16 END; 17 GO 18 --End of trigger definition 19 20 SELECT id FROM t6; 21 --IDs empty. 22 23 SELECT id FROM t7; 24 --ID is empty. 25 26 --Do the following in Session 1 27 INSERT t6 DEFAULT VALUES; 28 SELECT @@IDENTITY; 29 /*Returns the value 100. This was inserted by the trigger.*/ 30 31 SELECT SCOPE_IDENTITY(); 32 /* Returns the value 1. This was inserted by the 33 INSERT statement two statements before this query.*/ 34 35 SELECT IDENT_CURRENT('t7'); 36 /* Returns value inserted into t7, that is in the trigger.*/ 37 38 SELECT IDENT_CURRENT('t6'); 39 /* Returns value inserted into t6. This was the INSERT statement four statements before this query.*/ 40 41 -- Do the following in Session 2. 42 SELECT @@IDENTITY; 43 /* Returns NULL because there has been no INSERT action 44 up to this point in this session.*/ 45 46 SELECT SCOPE_IDENTITY(); 47 /* Returns NULL because there has been no INSERT action 48 up to this point in this scope in this session.*/ 49 50 SELECT IDENT_CURRENT('t7'); 51 /* Returns the last value inserted into t7.*/
参考:http://msdn.microsoft.com/zh-cn/library/ms175098(SQL.105).aspx