RE: Transaction within a Transaction

Tom, You could create a temporary table *with no log* before you start the transaction. Insert into that temp table all the control information that you want to keep. Then, when the transaction is rolled back you simply copy the information from your temp table into a permanent table for future analysis. HTH Tino > -----Original Message----- > From: owner-informix-list@iiug.org > [mailto:owner-informix-list@iiug.org]On Behalf Of Tom Jones > Sent: Tuesday, March 07, 2000 9:51 AM > To: informix-list@iiug.org > Subject: Transaction within a Transaction > > > IDS 7.31.TC5 on NT 4.0 SP 5. > > We have an application that, within the space of a single financial and > database transaction, performs a number of lookups and updates > (nothing like > rocket science there then...). A 'control' row is created right > at the start > of the transaction, and at certain stages, records a date and time and the > stage the transaction is at, until at the end of the entire > transaction, all > of the details are committed. > > If, however, during the life of the transaction, it falls over (either > programmatically or otherwise) we still want to be able to record > the dates > and times and the stage it was at at the time of failing, such that these > details won't be ROLLED BACK should any problems arise (this is so that we > can determine how far the actual transaction got before rolling back). > > Now, I know that there are many ways (to leave your lover) to > program such a > tracking system, but in essence, my question is:- > > How can you commit data to a table during a transactions that isn't > committed until the entire transaction is successful ?? > > We have discussed using STORED PROCS to call SYSTEM commands to run a > separate UPDATE script to update the relevant control record but we think > the row may be locked as its in the current transaction (being > executed from > the parent process that called this routine). > > If any of you know of such a method to update a row within a table that is > already being used within a transaction, please let me know. > > Cheers > > Sean > > > >