SQL TRIGGER

Question

SQL TRIGGER

martin.edward

SSCommitted

Points: 1645
More actions
September 8, 2010 at 2:32 am

#240098

Dear Experts,
I would like to create a trigger that makes an update only on INSERT of a data record. I have created one but it tends to update all the rows of the particular table which could lead to performance issues should the table grow to over a million records.
I am using AFTER INSERT command, I think this could be the problem. Is there a different command that would restrict the trigger to work only on the given row record and not update all table rows ?
Kind Regards,

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

R.P.Rozema SSChampion Points: 12317 More actions · Answer 1

An after insert trigger is exactly what you need. You've probably just forgotten to use the table Inserted to identify which rows were affected by the insert statement.

i.e. you need something like this:

create trigger taiMyTable

on dbo.MyTable

for insert

not for replication

as

begin

if @@rowcount = 0

return;

set nocount on;

update c

set

colX = i.colY

from dbo.My2ndTable c

inner join Inserted i on (i.ID = c.ID)

end

Functionally, what this trigger does is update colX in dbo.My2ndTable with the value you just inserted into colX in dbo.MyTable on the rows that have the same ID value in both tables.

Posting Data Etiquette - Jeff Moden[/url]
Posting Performance Based Questions - Gail Shaw[/url]
Hidden RBAR - Jeff Moden[/url]
Cross Tabs and Pivots - Jeff Moden[/url]
Catch-all queries - Gail Shaw[/url]

If you don't have time to do it right, when will you have time to do it over?

martin.edward SSCommitted Points: 1645 More actions · Answer 2

Thanks, but what does the part :- 'for insert not for replication mean'. On which table is the trigger on ?

Nakul Vachhrajani SSChampion Points: 10294 More actions · Answer 3

martin.edward (9/14/2010)
Thanks, but what does the part :- 'for insert not for replication mean'. On which table is the trigger on ?

The trigger is on the table specified after the "ON" keyword. For full details, please refer: http://msdn.microsoft.com/en-us/library/aa258254(SQL.80).aspx

Thanks & Regards,
Nakul Vachhrajani.
http://nakulvachhrajani.com

Follow me on
Twitter: @sqltwins

Hardy21 SSCrazy Eights Points: 9708 More actions · Answer 4

martin.edward (9/14/2010)
Thanks, but what does the part :- 'for insert not for replication mean'. On which table is the trigger on ?

Trigger is for Insert operation. This trigger is not executed during replication if you have setup the replication.

You can refer Nakul's link for more details.

Thanks

sanmatrix SSC Veteran Points: 250 More actions · Answer 5

why are you using trigger? where is your sql insert code. why don't you put the update code after the insert code? if possible then do this and get rid of the trigger.

you must know that triggers are not good and should be avoided as much as possible. use triggers only when no option is left or any other option is much costlier than using trigger.