September 26, 2008 at 12:14 pm
Hi all,
how to update two tables( with foreign key relation ship) at a time using triggers.
here is my code:
create trigger dep_update
on department
for update
as
if update(department_id)
begin
update employee set
department_id=i.department_id
from employee e inner join
inserted i on
e.department_id=i.department_id
end
Please help me as iam new to sql server
Thanks
sangi.
September 26, 2008 at 12:41 pm
That trigger looks like it should work, at least at first glance. Can you tell me what you're trying to resolve with it? Does it give you an error?
Also, have you looked at the "on update cascade" option for foreign keys? That makes it so that, if you change the value in the parent table, it gets changed automatically in the child table.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
October 2, 2008 at 7:40 pm
I think you have a problem in the trigger because if you are updating department_id in departments you won't have any rows in the employees table with the new department_id which is what is in the inserted table. What you want to do is update the records in the employees table which have the old department_id with the new department_id. So you need something like this:
Update dbo.employees
Set department_id = I.department_id
From
inserted I Join
deleted D On
I.key_value_other_than_id = D.key_value_other_than_id Join
dbo.employees E On
D.department_id = E.department_id
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
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply