Forum OpenACS Development: a Model to store Deleted Rows History


Did any one of you think of/implement a general dynamic DB Model to store a history for every deleted row in any table, something to work with an after delete trigger on each table.

My initial thinking is something like to store the filed name and field value.

Or this is a far impractical something to think about?

Posted by Jeff Davis on
ACS 3 used audit tables like that and they work (and are reasonably fast) so it's easy enough to do. Generally it's better to have a table which is a duplicate of the audited table to hold the deleted records (you can create a copy like that with "create table foo_audit as select * from foo where 0 = 1;").

With the content repository, cr_revisions just holds everything and you point at the live revision from cr_items. The disadvantage of that audit method is that you can't have unique constraints on cr_revisions columns (the advantage is rolling a revisions back or forth is trivial).

Posted by Samer Abukhait on
Deleted rows are meant to be history no more.. They should not be modified or deleted, so all constraints are valueless in them... ?

Plus, implementing one way in the system is much easier than creating a copy for every audited table??

I am talking about all tables in the DB, not only content or so; also an update history model is also in the same level to think in.
What you think?

If ACS 3 Model for auditing is available, can we check and discuss it?

Posted by Tom Jackson on

You can check the ecommerce package for one take on auditing.

Posted by Andrew Piskorski on
The ideal solution here is an RDBMS with really good native bi-temporal support, but AFAIK no such database exists, nor have I heard of anyone currently working on any such features.