Jump to content

Livy

Members
  • Posts

    2
  • Joined

  • Last visited

Everything posted by Livy

  1. 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?
  2. 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.
×
×
  • Create New...