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)
>
>    -- Get the value of the key/unique field that user wants to delete.
>    SELECT @C_KEY_VALUE FROM DELETED
>
>    -- Update Child 1
>    UPDATE my_child1_table SET foreign_key_fld = '' WHERE foreign_key_fld = @C_KEY_VALUE
>    -- Update Child 2
>    UPDATE my_child2_table SET foreign_key_fld = '' WHERE foreign_key_fld = @C_KEY_VALUE
>    -- Update Child 3,4, etc.
>
>    -- Now delete this row from the parent table
>   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.