Create trigger with try...catch in SQL Server 2005

Question

Hello, I've implemented the following trigger:

CREATE TRIGGER [OnContactDeleted]
    ON [TABLE].[Contact]
    INSTEAD OF DELETE
    AS 
    BEGIN
        SET NOCOUNT ON
        /*Store contact ID to variable*/
        Update [TABLE].[Account] Set [PrimaryContactID] = null where [TABLE].[Account].[PrimaryContactID] = (Select ContactID from Deleted)
        Delete from [TABLE].[Contact] where [TABLE].[Contact].[ContactID] = (Select ContactID from Deleted)
    END

It is clear a set of foreign keys out of another table and then delete the current record. This was done because setting the foreign key to null on delete does not work.

My question is about wrapping this trigger in a try...catch block where I can roll back if an exception occurs. Is that good practice and should I be doing it for this kind of trigger?

Thanks, Jason

Answer 1

I would rather leave this to fail and let the exception be handled in the consumer (the code performing a DELETE statement). There shouldn't be any logical reason within the context of this deletion and trigger for this operation to fail so it would therefore be an external factor causing the failure. Therefore the exception should be handled externally as well. Normally the DELETE would be taking place within a transaction too, so that is where the rollback would take place.