>>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
>
>
>
> 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.
"The creative process is nothing but a series of crises." Isaac Bashevis Singer
"My experience is that as soon as people are old enough to know better, they don't know anything at all." Oscar Wilde
"If a nation values anything more than freedom, it will lose its freedom; and the irony of it is that if it is comfort or money that it values more, it will lose that too." W.Somerset Maugham