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.*/