Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Keeping deleted records

Re: Keeping deleted records

From: Ram Srinivasan <>
Date: Thu, 9 Aug 2007 15:54:40 -0400
Message-ID: <>

  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.


Ram Srinivasan

On 8/9/07, Dennis Williams <> 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

Ram Srinivasan
Charlottesville, VA.

Received on Thu Aug 09 2007 - 14:54:40 CDT

Original text of this message