raiseerror sql server silent message

Question

How can I use ROLLBACK TRANS inside a SQL Server trigger without getting the standard message ?

"Msg 3609, Level 16, State 1, Line 1 The transaction ended in the trigger. The batch has been aborted."

This is being returned to the client. I would like the ROLLBACK to complete silently. I need to achieve this within the trigger only; i.e. no c# or .NET code changes to capture the error messagge.

Let me add that this is a 'dirty' workaround to a recently added functionality. When we added the new functionality the client didn't like it. Rather then do an emergency release of the codebase we just killed the update inside the trigger. The calling code is embedded SQL in the client app - not a stored proc.

Answer 1

Trouble is, the rollback inside the trigger means that the rows inserted/updated or deleted in the underlying table are back the way they were, which means that the database has failed to do what the original program has asked it to do.

Is your calling code updating the table directly, or calling a stored procedure which updates the table? The latter offers more scope for handling a rollback silently.

Answer 2

What you want is an INSTEAD OF trigger that doesn't actually do anything with the updated rows.