Re: Keeping deleted records
- From: "Dennis Williams" <oracledba.williams@xxxxxxxxx>
- To: wbfergus@xxxxxxxxx
- Date: Thu, 9 Aug 2007 12:53:37 -0500
Bill,
Our standard is to have an audit table for each production table that is
a configuration table. By that, I mean the smaller, low-volume tables. Each
production configuration table has a trigger that writes a record to the
audit table each time there is a change (insert, delete, update). The audit
table is a copy of the production table with three added columns, that store
the data for username who made the change, date and time the change was
made, and what the change was (insert, delete, update).
For the production "data" tables, most applications don't delete rows
from those anyway, and usually log all changes. For example, a payroll
program doesn't just change your salary, but writes the changes to a log
type file. So if you want to increase your salary, use SQL so there isn't
any trace. ;-)
I think this method is a good policy for everyone, with the value of
data stored in Oracle databases, SoX requirements, etc.
It saved the day a few years ago. Marketing loaded a new price table
without asking anybody. It had errors that rendered the production system
unusuable, which shut down all the warehouses. Rather than recover the small
table from backups, I was able to reconstruct it from the audit table and
got production up and going again.
Dennis Williams
Other related posts:
