Presumably you have one or more delete constraints before the parent record can be deleted, right?
This is probably just a matter of taste but personally I prefer a different approach to the situation you are facing. If the whole thing was a mistake, for instance an order which was created by accident, and no business events occurred, I like to just delete the parent and cascade the delete to the child table(s). I don't like nulling out the pointer from the child records. It's better IMO to set a status flag in the parent record so both it and its children are prevented from most if not all future actions. (One exception might be recalling them -- which would be mighty hard to do with the linkage intentionally broken). I just don't like orphan children floating around ;-)
>Hi,
>
>I am creating my first trigger that will set the value of all foreign keys to empty string ('') when user deleted a row in the parent table.
>
>Here is the draft of my code:
>
>
>ALTER TRIGGER [my_parent_delete]
> ON [dbo].[my_parent_table]
> INSTEAD OF DELETE
>AS
>BEGIN
> SET NOCOUNT ON;
>
> DECLARE @C_KEY_VALUE CHAR(10)
>
>
> SELECT @C_KEY_VALUE FROM DELETED
>
>
> UPDATE my_child1_table SET foreign_key_fld = '' WHERE foreign_key_fld = @C_KEY_VALUE
>
> UPDATE my_child2_table SET foreign_key_fld = '' WHERE foreign_key_fld = @C_KEY_VALUE
>
>
>
> delete from my_parent_table WHERE EXISTS
> ( SELECT * FROM DELETED WHERE my_parent_table.my_unique_fld = DELETED.my_unique_fld )
>
>END
>
>
>My question is, what if any of the child updates fails, how do I "trap" this error? TIA.