How to get ID from table in after insert Trigger ?

Posted by Nandkishorre under Sql Server on 8/6/2013 | Points: 10 | Views : 973 | Status : [Member] | Replies : 6
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




Responses

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

Up
0
Down
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

Up
0
Down
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

Up
0
Down
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

Up
0
Down
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

Up
0
Down

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

Up
0
Down
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

Login to post response