rmk
asked on
Best Practices for Error Handling in Triggers
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
(co11,...,coln)
SELECT col1,...coln
FROM deleted
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))
RAISERROR (@ErrMsg,16,1)
ROLLBACK TRANSACTION
RETURN
END
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?
INSERT INTO tblDeletedName
(co11,...,coln)
SELECT col1,...coln
FROM deleted
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))
RAISERROR (@ErrMsg,16,1)
ROLLBACK TRANSACTION
RETURN
END
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?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
You need to be aware that if you ROLLBACk you will also rollback the original transaction, in this case the delete.
Scott, I think that is the idea, no? If I can't save what I'm deleting I'd want it undeleted ..