Jump to content

Create an update trigger that cascades changes to refering table


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 post
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 post
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 post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...
×
×
  • Create New...