Re: Keeping deleted records

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_at_gmail.com> 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.

--
http://www.freelists.org/webpage/oracle-l