Difference between @@Identity,Scope_Identity() and Ident_Current()
Most of the developers are aware of this fact that above three @@Identity,Scope_Identity() and Ident_Current returns the Last value inserted.
.
So below i will explain the Difference between all 3 with Examples
--Creating sample table tbl1 with identity field
CREATE TABLE tbl(Empid int IDENTITY)
INSERT tbl1 DEFAULT VALUES
Case I-Single Table Insert without Trigger
--Run queries all will return 1
SELECT @@IDENTITY
SELECT SCOPE_IDENTITY()
SELECT IDENT_CURRENT('tbl' )
Case II-Table with Trigger
Now we will add a trigger in tbl that will have an insert action in table 2
--creating table 2
CREATE TABLE tbl0(Empid int IDENTITY(10,5))--start with 10 with increament of 5
--creating trigger to check behaviour of all 3
create trigger T_ins on Tbl
for insert
as begin
insert tbl0 default values
end
INSERT tbl DEFAULT VALUES
--This returns last value inserted across scope(second scope i.e trigger tbl0) which is tbl0 values 10
SELECT @@IDENTITY
--This returns the value inserted into identity column in curent scope that is tbl value 2
SELECT SCOPE_IDENTITY()
-This returns the last identity value generated for a specific table in any session and any scope
SELECT IDENT_CURRENT('tbl' ) --2
SELECT IDENT_CURRENT('tbl0' )--10
No comments:
Post a Comment