How to handle errors in a trigger?

Question

I'm writing some SQL code that needs to be executed when rows are inserted in a database table, so I'm using an AFTER INSERT trigger; the code is quite complex, thus there could still be some bugs around.

I've discovered that, if an error happens when executing a trigger, SQL Server aborts the batch and/or the whole transaction. This is not acceptable for me, because it causes problems to the main application that uses the database; I also don't have the source code for that application, so I can't perform proper debugging on it. I absolutely need all database actions to succeed, even if my trigger fails.

How can I code my trigger so that, should an error happen, SQL Server will not abort the INSERT action?

Additionally, how can I perform proper error handling so that I can actually know the trigger has failed? Sending an email with the error data would be ok for me (the trigger's main purpose is actually sending emails), but how do I detect an error condition in a trigger and react to it?

---

Edit:

Thanks for the tips about optimizing performance by using something else than a trigger, but this code is not "complex" in the sense that it's long-running or performance intensive; it simply builds and sends a mail message, but in order to do so, it must retrieve data from various linked tables, and since I am reverse-engineering this application, I don't have the database schema available and am still trying to find my way around it; this is why conversion errors or unexpected/null values can still creep up, crashing the trigger execution.

Also, as stated above, I absolutely can't perform debugging on the application itself, nor modify it to do what I need in the application layer; the only way to react to an application event is by firing a database trigger when the application writes to the DB that something has just heppened.

Solution

If the operations in the trigger are complex and/or potentially long running, and you don't want the activity to affect the original transaction, then you need to find a way to decouple the activity.

One way might be to use Service Broker. In the trigger, just create message(s) (one per row) and send them on their way, then do the rest of the processing in the service.

If that seems too complex, the older way to do it is to insert the rows needing processing into a work/queue table, and then have a job continuously pulling rows from there are doing the work.

Either way, you're now not preventing the original transaction from committing.

Comments

[Nikola Markovinović]

You might use TRY ... CATCH inside trigger to ignore, but log errors.

[Damien_The_Unbeliever]

You don't have the schema available? What does this mean? Surely you're able to inspect the tables/columns (if not, how have you even started this task?)

[Massimo]

I mean, there's no documentation at all; I have to find on my own what the database structure is. And it's quite a mess (I've tried diagramming it, and it was painful).

[Massimo]

I can only debug the trigger when it gets called from the application with real data... but then, I'm not there with a debugger. This is why I need a way for it to handle the errors on its own (and notify me so that I can fix them).

[Tony Hopkinson]

So your only real option is try catch round the trigger code, log, and then swallow. Followed of course by close eyes tight while praying loud an often. If it fails, there will be an exception it should appear in some log somewhere, unless someone esle has done try catch swallow of course. It's up to you, but I'd set up a test environment as in a copy of the database with the trigger, and then try and break it, rather than cross my fingers and hope nothing else does. Crossing my fingers has never worked for me, not once.