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

marc_s · Accepted Answer · 2012-06-08 16:26:11Z

2

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.

edited Jun 8, 2012 at 16:26

answered Jun 8, 2012 at 16:02

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Andrew Over a year ago

,thanks for ur suggestions. any advice on how to capture all events attempting to modify data of a certain table, even if the attempt fails.

Paul Over a year ago

Wrong start assumption... make sure the trigger will NEVER fail. An error in a trigger actually makes it impossible to update/insert/delete the data. So... if you want to log AS WELL as process the log make the recording and the processing independent. Fill the log from within the trigger (that's the place to guarantee that logging will always be done), then OR process the log time-based OR have a look at service brokers (send a message from within the trigger). The latter will AND guarantee indecency AND guarantee immediate processing.

Collectives™ on Stack Overflow

Logging inside the trigger

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related