---------- Original Message -----------

> Hmm, cannot this be solved by having one additional table? Let's say
> you have your CVS table and one more table TopLevelCommitted. Your
> TopLevelCommitted table should just contain one field filled by a
> generator and the same field should be in your CVS table as well. As
> soon as your 'top level' transaction starts you grab a number from the
> generator and for each of your 'sub-transactions' you use this number
> with the CVS table (each 'sub-transaction' being a transaction of
> their own). At the end, you insert into TopLevelTransaction and
> commit.
>
> To select from the CVS table, you always do
>
> SELECT <columns>
> FROM CVS
> JOIN TopLevelCommitted TLC
> ON TLC.GeneratedNumber = CVS.GeneratedNumber
> WHERE ...
>
> That way, no-one gets to the changes until your top level transaction
> is committed and if it never commits you just have orphan records that
> doesn't do more harm than wasting space. This is just a vague
> description of a not all to thoroughly thought through idea and
> refinements would have to be done to implement it. I'm not advocating
> against sub-transactions (I don't know enough about them to have much
> of an opinion), just saying that I think your CVS example could be
> solved without them.
>
> Set
> -I support Firebird, whether or not it has sub-transactions. That's
> why I am a member of The Firebird Foundation, which you can join at
> http://www.firebirdsql.org/ff/foundation

------- End of Original Message -------

That's pretty much equivalent to hacking around the system-provided transactional facilities. It's
true that in an environment where you're purely inserting new records, you can effectively flag
rows as being deleted, committed, or pending. In more complicated examples involving updates to
existing rows, you'll have trouble doing an update that leaves old data intact but presents new
data for confirmation by a clean-up transaction, unless you're keen on designing all the tables in
your database to contain both "base" and "diff" rows, to be aggregated when finding out
what's "really there", etc. (equivalent to what firebird does internally for you.)

I suppose it's possible otherwise too: provide a separate table full of "things to do", which the
final transaction picks up on and applies via SP's, or some other method -- including deletions,
updates, inserts, etc. The final transaction would just be an aggregator, and would act atomically
(as expected). The fate of those pending updates should something go wrong during their creation
remains to be seen.

Let's just say that I would prefer to have my transactional system do transactions for me. I've had
to deal with paradox and its odd way of doing things (dummy insertion rows, which you can't see but
will block other insertions), and I imagine it's about par for any other hack around transactions.
Just a thought.

Thanks for responding!

-Philip