Identity

DROP TABLE test_identity
CREATE TABLE test_identity
(
sno int identity(1,1),
emp_name varchar(10)
)
go

DROP TABLE test_identity_1
CREATE TABLE test_identity_1
(
sno int identity(1,1),
emp_name varchar(10)
)
go

/*Check the identity value for these two inserts*/
INSERT INTO test_identity
select 'moov'

SELECT @@IDENTITY,SCOPE_IDENTITY()

INSERT INTO test_identity
SELECT 'kc'
SELECT @@IDENTITY,SCOPE_IDENTITY()

/*Creating Trigger*/
CREATE TRIGGER Trigger_Identity ON test_identity FOR INSERT
AS
BEGIN

   INSERT INTO test_identity_1
SELECT 'ANAND'

END
go

/*Now Check the Identity*/
INSERT INTO test_identity
select 'Vicky'
SELECT @@IDENTITY,SCOPE_IDENTITY()


/*Run this in seperate Window*/
INSERT INTO test_identity
select 'AS'

/*Run this here*/
SELECT @@IDENTITY,SCOPE_IDENTITY(),IDENT_CURRENT ('test_identity')


-- So,@@IDENTITY and SCOPE_IDENTITY() will give current session identity values.. But @@IDENTITY will be affected by Triggers used in table
-- So, Always use SCOPE_IDENTITY() to avoid errors
-- IDENT_CURRENT ('TableName') gives current identity value of specified table regardless of the session

/********** Thats All Today **********************/