I am having a conflict between auditing and transactions, which seems to indicate a fundamental design problem. I would like to know whether some of you have had the same problem, and how you deal with it.

It turns out that for doing certain processes - notably, importing data to my system, sources are mainly Excel or text files - I use transactions, so that if anything goes wrong, I can undo changes. The typical "all-or-nothing" requirement.

Now, any change done to any table will trigger changes to an audit table, that is, a list of what changes were made, who did the changes (in this case, that would be whoever invoked the process), and when.

The problem, now, is that this is not very friendly for a multi-user environment. Any transaction will block the audit table - it took me a while to figure out what was going on! - and no other user can save changes.

I already improved the error handling, in the sense that the user gets a message stating that probably some process is running, and to try again later.

I have considered creating a second auditing table, just for the longer processes. I could control, through a variable, which auditing table will be used - and I would have to merge the auditing tables eventually.

Any additional ideas?

TIA,

Hilmar.