How to handle errors in a trigger?

Question

I am writing SQL code that needs to be executed when rows are inserted into a database table, so I use the AFTER INSERT trigger; The code is quite complicated, so there may be some errors.

I found that if an error occurs when the trigger starts, SQL Server aborts the package and / or the entire transaction. This is not acceptable to me, as it causes problems for the main application using the database; I also do not have the source code for this application, so I cannot properly debug it. I absolutely need all the database actions to succeed, even if my trigger does not work.

How can I encode my trigger so that SQL Server does not abort INSERT when an error occurs?

Also, how can I do the correct error handling so that I can really know that the trigger did not work? Sending emails with error data will be approved for me (the main purpose of the trigger is sending emails), but how to determine the error condition in the trigger and respond to it?

---

Edit:

Thanks for the tips on optimizing performance using something other than a trigger, but this code is not "complicated" in the sense that it is long or intense; it just creates and sends an email message, but for this it needs to retrieve data from different related tables, and since I am reverse-engineering this application, I do not have an accessible database schema and am still trying to find my path around it; this is why conversion errors or unexpected / null values ​​can still fire, trigger failure.

Also, as indicated above, I absolutely can not debug the application itself , nor can I change it to do what I need at the application level; the only way to respond to an application event is to trigger a database trigger when the application writes to the database that something just cleared.

Answer 1

If the operations in the trigger are complex and / or potentially taking a long time, and you do not want the activity to affect the original transaction, you need to find a way to separate the activity.

One way could be to use Service Broker . In the trigger, just create messages (one per line) and send them in your path, and then do the remaining processing in the service.

If this seems too complicated, an older way to do this is to insert rows that need processing into the work / queue table, and then run a job that continuously pulls rows from them that do the work.

In any case, now you do not interfere with the initial transaction.

Answer 2

Triggers are part of a transaction. You could try to catch a swallow around the trigger code or a slightly more professional attempt to catch a log trap, but in fact you should let it move and then fix the real problem, which can only be in your trigger. If none of the above is acceptable, you cannot use a trigger.