>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.
Hi Hilmar,
I always do all the processing in table-buffered tables and I only put save process (tableupdate) in transactions. Sorry if this is not helpful.
If it's not broken, fix it until it is.
My Blog