>>>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.
>>
>>The book on SQL Server 2005 I using says that "Constraint processing - including Check constraint, UNIQUE constraint, and PRIMARY KEY constraint - happens after the INSTEAD OF trigger. But in my case the FOREIGN KEY constraint on the table affected by the UPDATE fires before the trigger finishes. Puzzling.
>
>Nothing too puzzling. The constraint checks occur after the INSTEAD OF trigger but only for the main table. For the child table constraints happen right away if there is no INSTEAD OF trigger on the child table.
>
>In other words, you're probably in the catch 22 situation now.

You are probably right (most likely). So I need to find a way to jump through this hoop too.