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?
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