Object-oriented non-linear undo.

[Daniel]

Hi,

Sometimes users input incorrect data or delete records by mistake.
Correcting these may be tedious; especially if they aren't immediately
detected. Therefore, I want to provide means to detect and correct such
errors.

Example:
Say I have a database in which I store information about cars. Now, somebody
notices that the information about car A is incorrect. Then I want to be able
to list the history of changes made to information related to car A (this
information is distributed across several tables). When I find the point when
the incorrect information was introduced, I want to be able to list changes
to other cars made around that moment in time, to see if there were more
errors introduced. Finally, I want to be able to restore the state of the
information for car A (and possibly other cars) without affecting the
numerous other changes to the database that has been made since (the
incorrect data might have been entered up to perhaps a year ago).

Obviously, there is no out-of-the-box solution to do this in Access, so I
will have to provide the logic and structure myself. I have some ideas about
how to implement this, but before I dive in head first I would like to learn
from others mistakes and wisdom on the matter. If anyone would give me any
pointers about papers to read, keywords to google for etc I would appreciate
it very much. Other thoughts or comments are also welcome.

/Daniel

 

[Allen Browne]

Daniel, while that ideal is noble, I don't see that it will be practical.

Firstly, Access (JET) does not give logs, and does not expose triggers. If
all inserts, deletes, and edits are made through forms, and there are no
cascading relations or replication syncs, it is possible to use the events
of the form to create a transaction log. The deletions are the most awkward,
as it is possible to delete several records at once. If you are interested
in following that through, see:
Audit Trail - Log changes at the record level
at:
http://allenbrowne.com/AppAudit.html

The second stage would be to write code to rollback to a particular point in
the past. That's doaable, but messy. You might find at this stage that you
are better of using SQL Sever tables than JET, since that gives you full
transaction logging and rollbacks without the need to code it and rely on
the form events.

But you want to do selective rollback. In my way of thinking that's
logically inconsistent. Example:
Car A is deleted.
It's a Sports car.
All sports cars now have a 10% rise in price.
Car A is restored.
Did you also need to give it a price rise?

In other words, is there an entire queue of things that could have affected
car A while it was gone, that need to be applied how it is back again? How
do you manage that queue? Deciding which ones are relevant, and in which
order is not simple. Deciding how to perform multiple interacting and
potentially conflicting undos in any order is, I think, impossible.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

message

 

[Daniel]

Hi Allen,

Thank you for your very valuable answer.

The second stage would be to write code to rollback to a particular point in
the past. That's doaable, but messy. You might find at this stage that you
are better of using SQL Sever tables than JET, since that gives you full
transaction logging and rollbacks without the need to code it and rely on
the form events.

Actually, I already use SQL Server to store the data. I use Access as a GUI.
It's true that transaction logs would be more "clean" but it doesn't really
provide the functionality I'm after.

But you want to do selective rollback. In my way of thinking that's
logically inconsistent. Example:
Car A is deleted.
It's a Sports car.
All sports cars now have a 10% rise in price.
Car A is restored.
Did you also need to give it a price rise?

In other words, is there an entire queue of things that could have affected
car A while it was gone, that need to be applied how it is back again? How
do you manage that queue? Deciding which ones are relevant, and in which
order is not simple. Deciding how to perform multiple interacting and
potentially conflicting undos in any order is, I think, impossible.

Good point. I probably should settle for providing a nice view of the
history of changes for the particular "entity" (car in my example). Of course
I could also provide the option to reset the entity to it's value at a
particular time, but then it is the responsibility of the person issuing this
command to inspect the resulting values so that they are valid.

... it is possible to use the events
of the form to create a transaction log. The deletions are the most awkward,
as it is possible to delete several records at once. If you are interested
in following that through, see:
Audit Trail - Log changes at the record level
at:
http://allenbrowne.com/AppAudit.html

Very interesting, I'll have a good look at this.