>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?

How about redirecting the auditing of the import into a readwrite cursor based on the audit table, then when import is done, append from it into the audit table? You could wrap it into two levels of transaction (well, maybe), the inner handling the import only, and if it goes right, then do the append audit too. Since the audit isn't really triggering anything else, the append should be smooth and fast.