>>Hi,
>>
>>Something does not work in my INSTEAD OF UPDATE TRIGGER. Here is the segment of my code:
>>
>>

>>
>>declare @C_ID_BEFORE CHAR(15)
>>declare @C_ID_AFTE CHAR(15)
>>
>>IF UPDATE(unique_fld)
>>    BEGIN
>>
>>    SELECT @C_ID_BEFORE = unique_fld FROM deleted
>>    SELECT @C_ID_AFTER  = unique_fld FROM inserted
>>
>>    UPDATE PARENT_TBL SET unique_fld = @C_ID_AFTER
>>				WHERE unique_fld = @C_ID_BEFORE
>>
>
>>    UPDATE CHILD_TBL SET unique_fld = @C_ID_AFTER 
>>				WHERE unique_fld = @C_ID_BEFORE
>>
>>END
>>

>>
>>The error message I get is that "The UPDATE statement conflicts with the REFERENCE constraint ..."
>>
>>What am I doing wrong? TIA.
>
>You cannot update a reference key
>and fix this it with a INSTEAD TRIGGER,
>and even if it were possible,
>you can not write the trigger with support a multirow update
>because you cannot join deleted and inserted tables!
>
>

>
>declare @C_ID_BEFORE CHAR(15)
>declare @C_ID_AFTE CHAR(15)
>
>IF UPDATE(unique_fld)
>    BEGIN
>
>    SELECT @C_ID_BEFORE = unique_fld FROM deleted
>    SELECT @C_ID_AFTER  = unique_fld FROM inserted
>
>    UPDATE PARENT_TBL SET unique_fld = @C_ID_AFTER
>				WHERE unique_fld = @C_ID_BEFORE
>
> -- CHILD_TBL.unique_fld = @C_ID_BEFORE is an orphan here!
>
>    UPDATE CHILD_TBL SET unique_fld = @C_ID_AFTER 
>				WHERE unique_fld = @C_ID_BEFORE
>
>END
>

>
>with a single row update,
>you can fix the error with an UPDATE conversion to DELETE + INSERT
>or disabling the FOREIGN KEY before the parent UPDATE and enabling the FOREIGN KEY after the child UPDATE.
>
>BUT!
>
>the solution is very simple with SQL Server 2005-2008
>
>

>ALTER TABLE CHILD_TBL WITH CHECK ADD CONSTRAINT [FK_CHILD_TBL_PARENT_TBL] 
>FOREIGN KEY(unique_fld) REFERENCES PARENT_TBL (unique_fld)
>ON UPDATE CASCADE
>

Thank you.