>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