Re: Keeping deleted records

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_at_gmail.com> 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_at_trivadis.com
http://www.trivadis.com

=========================

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