Bill How about adding a column "DELETED" to the tables, and mask the original structure using views and instead of triggers, that just flag the new field instead of a delete. Stefan On 8/9/07, Bill Ferguson <wbfergus@xxxxxxxxx> wrote: > > 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 > -- ========================= Stefan P Knecht Consultant Infrastructure Managed Services Trivadis AG Europa-Strasse 5 CH-8152 Glattbrugg Phone +41-44-808 70 20 Fax +41-808 70 12 Mobile +41-79-571 36 27 stefan.knecht@xxxxxxxxxxxx http://www.trivadis.com =========================