How to get ID from table in after insert Trigger ?

Posted by Nandkishorre under Sql Server on 8/6/2013 | Points: 10 | Views : 1041 | Status : [Member] | Replies : 6

Write New Post |

Search Forums | Answered

Resolved Posts |

Un Answered Posts |

Forums Home

Hi to all,

I create after insert trigger on EMP1 table.In EMP1 table i don't have identity column, in that trigger i insert data into EMP2 table.In EMP2 table i have identity (ID)column with auto increment 1. After insert the data into EMP1 data automatically inserted in EMP2 and also created a ID fot that employee. in this case how to get that ID from EMP2 table in that trigger ?

could anyone know about this tell me.

Regards
Nanda Kishore.CH

Reply | Reply with Attachment

Alert Moderator

Responses

Posted by: Bandi on: 8/6/2013 [Member] [MVP] Platinum | Points: 25

0	CREATE TABLE Student ( Roll int identity, name varchar(10), Marks int) GO CREATE TABLE Student1 ( Roll int identity, name varchar(10), Marks int) GO CREATE TRIGGER TestIDTri ON Student AFTER INSERT AS BEGIN INSERT Student1(Name, Marks) SELECT Name, Marks FROM inserted SELECT SCOPE_IDENTITY() LogTabIdentity END GO Case2: We can directly get the @@IDENTITY value from trigger CREATE TRIGGER TestIDTri ON Student AFTER INSERT AS BEGIN INSERT Student1(Name, Marks) SELECT Name, Marks FROM inserted END GO insert Student values('chandu', 100) SELECT @@IDENTITY -- this will return Identity value from trigger because of after trigger refer this link for explanation http://www.databasejournal.com/features/mssql/article.php/3931466/ObtainingIdentityColumnValuesinSQLServer.htm Note: Better to use stored procedure instead of Triggers. Triggers will lead to several problems Mark This Response as Answer -- Chandu http://www.dotnetfunda.com/images/dnfmvp.gif Nandkishorre, if this helps please login to Mark As Answer. \| Alert Moderator

CREATE TABLE Student ( Roll int identity, name varchar(10), Marks int)

GO

CREATE TABLE Student1 ( Roll int identity, name varchar(10), Marks int)

GO

CREATE TRIGGER TestIDTri

ON Student

AFTER INSERT 

AS

BEGIN

	INSERT Student1(Name, Marks) SELECT Name, Marks FROM inserted

	SELECT SCOPE_IDENTITY() LogTabIdentity

END

GO

Case2:
We can directly get the @@IDENTITY value from trigger


CREATE TRIGGER TestIDTri

ON Student

AFTER INSERT 

AS

BEGIN

	INSERT Student1(Name, Marks) SELECT Name, Marks FROM inserted

END

GO

insert Student values('chandu', 100)

SELECT @@IDENTITY    -- this will return Identity value from trigger because of after trigger

refer this link for explanation
http://www.databasejournal.com/features/mssql/article.php/3931466/ObtainingIdentityColumnValuesinSQLServer.htm

Note: Better to use stored procedure instead of Triggers. Triggers will lead to several problems

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Nandkishorre, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Bandi on: 8/6/2013 [Member] [MVP] Platinum | Points: 25

0	Have you tested the above code? It that helps you, please mark as answer Mark This Response as Answer -- Chandu http://www.dotnetfunda.com/images/dnfmvp.gif Nandkishorre, if this helps please login to Mark As Answer. \| Alert Moderator

Posted by: Nandkishorre on: 8/6/2013 [Member] Starter | Points: 25

0	Thank you for your response. I solved myself. Nandkishorre, if this helps please login to Mark As Answer. \| Alert Moderator

Posted by: Bandi on: 8/6/2013 [Member] [MVP] Platinum | Points: 25

0	How you solved the issue... you can help others by posting your fix too.... If mine also helped you, mark that reply as answer.. Mark This Response as Answer -- Chandu http://www.dotnetfunda.com/images/dnfmvp.gif Nandkishorre, if this helps please login to Mark As Answer. \| Alert Moderator

Posted by: Nandkishorre on: 8/6/2013 [Member] Starter | Points: 25

0	answer is : select @id = id from emp2 where empname = @empname Nandkishorre, if this helps please login to Mark As Answer. \| Alert Moderator

Posted by: Bandi on: 8/6/2013 [Member] [MVP] Platinum | Points: 25

0	You can simply use SELECT @@IDENTITY or SCOPE_IDENTITY() -- gives you the accurate results... select @id = id from emp2 where empname = @empname will give you the last record ID value in the case of duplicate employee name... ( in real time more than one employee can have same name) Mark This Response as Answer -- Chandu http://www.dotnetfunda.com/images/dnfmvp.gif Nandkishorre, if this helps please login to Mark As Answer. \| Alert Moderator

You can simply use SELECT @@IDENTITY or SCOPE_IDENTITY() -- gives you the accurate results...
select @id = id from emp2 where empname = @empname
will give you the last record ID value in the case of duplicate employee name... ( in real time more than one employee can have same name)

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Nandkishorre, if this helps please login to Mark As Answer. | Alert Moderator

Latest Posts