Re: Keeping deleted records
- From: "Ram Srinivasan" <srinivasanram2004@xxxxxxxxx>
- To: oracledba.williams@xxxxxxxxx
- Date: Thu, 9 Aug 2007 15:54:40 -0400
All:
I would go with the audit table approch. When I used to work on clinical
trials, everything they do to any row in any table goes into an audit
table. Right from the insertion of a new row until the database gets
closed, everything goes into this audit table. So we can retrieve the old
data, we can find who deleted or updated a given row, etc. This was because
in clinical trials, it is mandatory that you keep all these because the FDA
stipulates it.
Thanks.
Ram Srinivasan
----------------------------------------
On 8/9/07, Dennis Williams <oracledba.williams@xxxxxxxxx> wrote:
>
> 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
>
--
Sincerely
Ram Srinivasan
Charlottesville, VA.
Other related posts:
