Alexandre Benson Smith wrote:

>At 12:12 02/04/2004 +0200, you wrote:
>
>
>>I've had this sort of experience too! :-)
>>I agree that transactions go a long way to avoiding such problems. OTOH,
>>I have also know a few cases where a customer phones me up to say that
>>they deleted a record by mistake. I try to be polite and not ask the
>>obvious "err... why did you do that?!?"... :-) So I ended up having to
>>restore a backup, and pump an old version of the record back to the DB.
>>So it went ok, but I can understand the potential usefulness of some
>>sort of "Undo-after-commit" feature, or something like that...
>>
>>Or else, what might be good would be something like a shadow file, in
>>which, all obselete record versions are dumped, as and when they become
>>obselete. Then, there could be a simple API (or perhaps an SQL
>>extention) that could be used for loading old record versions back into
>>the DB, and also for purging out old versions periodically...
>>
>>Please, no flammes if this is yet another horrendous idea! :-)
>>
>>Jonathan Neve.
>>
>>
>
>Hi Jonathan,
>
>What about if you create a history table, or dead_table, that will be feed
>by an after delete trigger on the mains table, this dead records table
>should have the same structure plus aditional information (like delete
>date/time, user, anything you think you need to keep).
>
>If you need to restore it... just pump it back from the dead_records_table
>to the main_table.
>
>Or create a flag on the records marking it as deleted, and from time to
>time delete all records that are marked as deleted and are delete after X
>days...
>
>

Yes, this isn't a bad idea, but it's a lot of work. I do this for a few
of the main tables in many of my projects. But it's no fun, because it
has to be deleted (not apprear in the stats, not appear in any of the
lookup lists, etc...), without really being deleted at all. So it's
certainly no very big deal in most cases, but I would like to have to do
that sort of thing for all my tables!

Also, this doesn't go quite as far as what I was suggesting, since you
can only restore deleted records, not old versions of updated records...

>I use to have an undo method in my systems, it's usual that a delet is just
>not a delete from a table, the delete should be a process, when you delet
>something this should modify other tables, sums, totals, and so on... so if
>this deletion is wrong, the hole "delete process"should be reverted. So I
>have a Process to Delete/Undelete a record.
>
>

That sounds pretty good. I've never pushed things that far. OTOH, it
also sounds like a lot of work!
Adding a feature to engine is probably a lot of work too of course, but
at least you only have to do it once! :-)

>One other thing, I think I don't have a lot of tables that don't have
>references (referential integrity, or forced by some system rule) with
>other tables, and this references should stop a deletion.
>
>

Sure. Although much of this is handled automatically with a foreign key,
provided you don't use cascading deletes.

Thanks for your input,

Jonathan Neve.


[Non-text portions of this message have been removed]