Logging inside the trigger

Question

I'm trying to code a SQL Server 2000 trigger.

For each insert statement, I would like to log the inserted value into a log table, and then do some processing. How can I prevent the logging from being rolled back when the processing fails?

That's it seems I need an inner autonomous transaction to put the logging inside it. Is it doable with T-SQL on SQL Server 2000?

I hope the implementation does not cause too much overhead. Or if the processing fail, how can I gracefully manage it and do not make it rollback the origin insert. thanks

Answer 1

I would strongly recommend not to do any processing of any kind in a trigger.

You cannot control when and how often a trigger is fired - therefore, make sure your triggers are very lean - at most, insert a row (or a few rows) into an "audit" or "job to process" table, as a "marker" for later processing. But don't actually start/do the processing right in the trigger.

Handle the actual processing separately, using e.g. a SQL Agent Job or something. Since that'll be run independently of your original statement, you won't have any issues with "nested" transactions and stuff like that, either.