trigger problem

Question

trigger problem

mzakwans

Grasshopper

Points: 22
More actions
December 11, 2008 at 3:23 am

#155009

hi all, I have a big problem with the trigger
when I tray to create trigger to update records in table tb2 when any change are made in table tb1
it's repeat all records again but I want to update the record when I do update, insert or delete any record and the trigger is:
create trigger add_xxx
on student
for insert, update, delete
as
insert into school
select ID, name, class from student

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

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

CrazyMan SSChampion Points: 12560 More actions · Answer 1

insert into school
select ID, name, class from student

Hi you are inserting all the rows from Student table when ever you fire a trigger, you have to use values from Inserted or Deleted

for EG:

For Deletion you can use

Select ID,name,class from Deleted

This will insert rows that are been changed from the table 🙂

brainy SSCrazy Points: 2445 More actions · Answer 2

create trigger add_xxx
on student
for insert, update, delete
as
insert into school
select ID, name, class from student

The code you provided is not sufficient. Please provide full code so that we can try to resolve this issue.

mzakwans Grasshopper Points: 22 More actions · Answer 3

hi

thanks

but I have tried it without delete and every time I do change on exist record it adds all record to the tb2 again

brainy SSCrazy Points: 2445 More actions · Answer 4

If you want to track changes ... you need to first check whether its insert, update or delete.

If insert the inserted table only will have record... if delete... only deleted table will have a record.

If update both tables will have one record... Inserted table will have new record.... Deleted table will have old values.

I think this will clear your doubts.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

mzakwans (12/11/2008)
hi
thanks
but I have tried it without delete and every time I do change on exist record it adds all record to the tb2 again

Have you referenced the deleted and/or inserted tables in your trigger?

If so, post the code. If not, you have to change the trigger to reference those tables as they are the ones that contain the rows that were added/changed/deleted

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jack Corbett SSC Guru Points: 184394 More actions · Answer 6

I recommend that you read this article[/url] before proceeding any further.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question