Jump to content

Create an update trigger that cascades changes to refering table


Livy

Recommended Posts

I have Table2 with a foreign key to Table1. I want to create a trigger that automatically update Table2 when Table1 is updated. Here is what's on my mind:

create trigger MyTrigger on Table1 instead of update asif UPDATE(Table1_ID)begin	declare @OldID as int, @NewID as int;	select @OldID=P_ID from deleted;	select @NewID=P_ID from inserted;	update Table2 set Table1_ID=@NewID where Table2.Table1_ID=@OldID;	update Table1 set Table1_ID=@NewID where Table1_ID=@OldID;end

I received an error from SQL Server indicating that there was a conflict with the foreign key constraint when I tried to update a row. So I modified it a bit:

create trigger MyTrigger on Table1 instead of update asif UPDATE(Table1_ID)begin	declare @OldID as int, @NewID as int;	select @OldID=P_ID from deleted;	select @NewID=P_ID from inserted;	update Table2 set Table1_ID=null where Table2.Table1_ID=@OldID;	update Table1 set Table1_ID=@NewID where Table1_ID=@OldID;	update Table2 set Table1_ID=@NewID where Table1_ID=null;end

It does work. But pre-existing null values from Table2 are obviously lost if the trigger runs. What is the correct way to do this? Note that I already know of the cascading option when creating table. I just want to do it using trigger. Thanks. Note: I don't know how to loop through the inserted and deleted tables when multiple rows are updated. I'm assuming 1 row is updated at a time.

Link to comment
Share on other sites

So you have a foreign key, but it might be null? If you can't use null as the temp value to replace, then you might want to use another value that wouldn't show up, maybe -1 if the field is unsigned, or 0. If that violates a constraint then consider adding a row to the other table with the same ID to use as a placeholder. Normally you would use null for that.

Link to comment
Share on other sites

It is an exam question to be honest. I have found a workaround for it. That is temporarily deleting the foreign key constraint, then re-creating it again. Of course it sounds stupid, but in reality, no one is insane enough to change the ID of a table. I guess I have to use stupid query to deal with stupid exam question. One thing though, how do I loop through the inserted and deleted table if multiple rows are updated? Is there any for loop in SQL?

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...