RE: Keeping deleted records

Why not just create a history table for each table and have delete
triggers on each table put the records in the history tables along with
deleted by date and user columns?  You wouldn't be able to create the
history tables with the same primary keys as the originals, but you'd be
able to relatively easily retrieve records manually or programatically
if necessary.
 

Bob Stauffer
DBA
D&E Communications
Ephrata, PA, USA
717-738-8737
rstauffer_at_decommunications.com


 


________________________________

From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Bill Ferguson
Sent: Thursday, August 09, 2007 12:31
To: oracle-l
Subject: Keeping deleted records


Hi all,
 
My management wants to keep a copy of all deleted records for historical
purposes, in case somebody decides at a later point that the deletion
was a mistake. This happens about once a year for probably a dozen or so
records. 
 
The data structure is about 35 tables consisting of one master table
with children attached to it. Only two of the child tables have their
own children. This primarily a scientific record type database, of known
worldwide mineral deposits, so the volume of transactions is fairly low,
compared to what most of you are used to dealing with. I might have a
couple hundred transactions per day on a busy day. 
 
I'm kind of torn between two approaches. The first approach is to
basically create a duplicate schema that contains blank table
structures, and as records are deleted, move them over to the "deletes"
schema and then delete them from production. This will entail of bunch
of redesign though on all of the triggers and others constraints in the
new schema. 
 
The second approach would be to have the "deletes" schema be a copy of
everything currently in the production schema, and then as new records
are added, add them to the "deletes" schema, and if they're deleted,
then I don't have to do anything. This approach though would entail the
reworki9ng of all of the existing triggers in the production schema. 
 
Either way, management hasn't said what kind of tracking (if any) that
they want for updates. It seems that they'd probably want that as well,
so if a production record was updated, the new version of the record
would be copied over to the "deletes" schema. 

Anybody else ever run across this kind of requirement before and have
any ideas or suggestions on the best/easiest way to handle it?
 
RMAN backups would really be a pain, as the deletes could have occured
at any time and over a huge period of time as well.
 
Thanks.
-- 
-- Bill Ferguson 


**DISCLAIMER
This e-mail message and any files transmitted with it are intended for the use of the individual or entity to which they are addressed and may contain information that is privileged, proprietary and confidential. If you are not the intended recipient, you may not use, copy or disclose to anyone the message or any information contained in the message. If you have received this communication in error, please notify the sender and delete this e-mail message. The contents do not represent the opinion of D&E except to the extent that it relates to their official business.

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