Re: Keeping deleted records

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

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