| Subject | Re: restoring to a point in time/replaying transactions |
|---|---|
| Author | Adam |
| Post date | 2006-06-06T02:59:32Z |
--- In firebird-support@yahoogroups.com, "markd_mms" <spam@...> wrote:
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.
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
>No, a transaction is atomic. It either entirely succeeds, or entirely
> 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?
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.
>Also delete triggers.
> 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?
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