A italian monk, Luciano Paccioli, came up with a method in the 16th
century (or there-abouts) that works well. The combination of double
entry book keeping and journaling of transactions, designed for pencil,
paper, and abacus, are a natural fit for RDBMS' in general.

It does mean that as programmers we need to reign in our natural
tendency to try to eliminate redundancy.

On Tue, 2006-06-06 at 02:59 +0000, Adam wrote:
> --- In firebird-support@yahoogroups.com, "markd_mms" <spam@...> wrote:
> >
> > i really don't think this is a good idea but someone made the
> > suggestion so i need to follow up on it.
> >
> > we maintain a catalogue of music and video releases and someone has
> > suggested that our new database be capable of being restored to a
> > point in time, so say someone stuffs up a mass update of data and
> it's
> > only discovered days later we can rollback all the transactions done
> > in the meantime. can this be done?
>
> No, a transaction is atomic. It either entirely succeeds, or entirely
> fails. You can not rollback a committed transaction. You can create a
> new transaction that undoes everything the earlier transaction did,
> which then commits.
>
> If you think I would want to undo all work I have done for the past
> few
> days because you made a mistake a week ago, I would be extremely
> annoyed. Sure I want to undo the affects of a given operation, but
> not
> necessarily subsequent operations.
>
> You would need to maintain an audit trail and some sort of reversing
> out strategy, with the knowledge that some data may have since been
> modified again. I imagine that is where the huge workload will come.
>
> >
> > i realise there's probably not much in the way of the database doing
> > this itself so i though we might create some history tables and
> insert
> > records into them in after insert/update triggers. in a trigger is
> > there any easy way of finding which field has been changed aside
> from
> > comparing each OLD and NEW field?
>
> Also delete triggers.
>
> Any insert or delete is a change. You only need to compare
> modifications for updates. You may need to approach the problem
> differently though, more like revisions in version control software.
> Rather than updating a record, insert another one with the new data.
> Rather than deleting a record, insert a record with a deleted flag
> set.
> (This happens under the hood with MGA anyway, but the scope of that
> operation is based on the transaction).
>
> Another small note, if the fields are nullable, then you will do well
> to remember that
>
> IF (OLD.FIELD1 <> NEW.FIELD1) THEN
> BEGIN
> -- WONT BE REACHED IF FIELD1 WAS AND STILL IS NULL
> END
>
> You need to use COALESCE, or in FB 2 you can use NOT DISTINCT FROM
>
> Adam
>
>
>
>
>