@@IDENTITY ==> Is
a system function that returns the last-inserted identity value for any table
in the current session, across all scopes.
SCOPE_IDENTITY()
==> Returns the last identity value inserted into an identity column for any
table in the current session and in the same scope.
IDENT_CURRENT ==>
Returns the last identity value generated for a specified table or view. The
last identity value generated can be for any session and any scope.
GO
-- create a main table
CREATE TABLE test_main(id INT IDENTITY (1,1), name VARCHAR(10))
GO
-- insert some records
INSERT INTO test_main(name) VALUES('A'),('B'),('C')
GO
-- create child table
CREATE TABLE test_child(id INT IDENTITY (1,1), name VARCHAR(10))
GO
-- create a trigger on main table to insert the reccords into child table
CREATE TRIGGER test_trigger ON test_main FOR INSERT
AS
BEGIN
INSERT INTO test_child SELECT name FROM INSERTED
END
GO
-- check the identity on main table
SELECT * FROM test_main
-- result
1 A
2 B
3 C
GO
-- now insert records into main table and check the identity
INSERT INTO test_main(name) SELECT 'D'
-- now check the below result
select @@IDENTITY, SCOPE_IDENTITY(),IDENT_CURRENT('test_main')
-- result
1 4 4
No comments:
Post a Comment