5

i am working on sql server, where i want to insert the record in a particular table say (a), this table contains two column [id (Identity Field) and name(nvarchar(max)] now after the records is inserted in table (a), a trigger should fire and insert the identity field value in table b.... i am using after insert trigger for this purpose but i am not getting how i would be getting the identity field value in trigger... which should be inserted in table b.

This is what i am using

create trigger tri_inserts on (a)
after insert
as
begin
   insert into b (id, name) values (?,?)
end

Please reply as soon as possible.. Thanks and Regards Abbas Electricwala

gbn
  • 422,506
  • 82
  • 585
  • 676
Abbas
  • 4,948
  • 31
  • 95
  • 161
  • See this question on how to get SQL identities and how each behaves: http://stackoverflow.com/questions/42648/best-way-to-get-identity-of-inserted-row – nybbler Jan 27 '11 at 18:49
  • @nybbler: this is on actual insert. In the INSERT trigger you'd read the INSERTED table – gbn Jan 27 '11 at 18:52

2 Answers2

13
create trigger tri_inserts on a
after insert
as
set nocount on

insert into b (id, name)
    SELECT id, name FROM INSERTED
GO
gbn
  • 422,506
  • 82
  • 585
  • 676
6

@gbn has the best solution, but I want you to understand why the SELECT clause is better than using a VALUES clause in a trigger. Triggers fire for each batch of records inserted/updated/deleted. So the inserted pseudotable or the deleted pseudotable may have one record or they may have a million. The trigger has to be able able to handle either case. If you use a values clause, you only get the action happening for one of the records out the the million. This casues data integrity issues. If you decide to loop through the records in a cursor and use the VALUES clause, your performance will be horrible when you get a large number of records. When I came to this job, we had one such trigger, it took 45 minutes to insert a 40,000 record insert. Removing the cursor and using a set-based solution based on athe SELECT clause (Although a much more complex one than the example)reduced the time for the same insert to around 40 seconds.

HLGEM
  • 94,695
  • 15
  • 113
  • 186