Best Practices for Error Handling in Triggers
Posted on 2004-08-10
I have a simple delete trigger in SQL Server 2000 that inserts the deleted record from tblName to a deleted table tblDeletedName. The code isssentially
INSERT INTO tblDeletedName
From a best practices perspective should I include an error test and explicitly issue a ROLLBACK TRANSACTION? Should I also issue a RAISERROR and if so does it go before or after the rollback. If I do not of these and an error occurs, will the trigger stop at the point of error and rollback the transaction?
At this time I'm using the following before the insert
DECLARE @ErrNbr INT
DECLARE @ErrMsg VARCHAR(250)
and the following after the insert
SET @ErrNbr = @@ERROR
IF @ErrNbr <> 0 BEGIN
SET @ErrMsg = 'tblName delete trigger failed on error ' + CAST(@ErrNbr AS VARCHAR(20))
The actual delete command for tblName comes from an ADO command in VBA in an Access XP mdb application. I'm still trying to figure out exactly what populates the Err object and the ADO Errors collection.
Are there any good books or references describing best practices for coding triggers and error handling in stored procedures?