Re: Delete cascade report

Make sure you charge them by the hour for the work done. It will be a hell of a program to write and maintain! :)

I don't think ORACLE provide the functionality you want since all records deleted are gone when you commit. Please examine the following suggestions and see if it works for you. I would love to be proven wrong and be told that ORACLE has this functionality, though.

Method 1:
You can probably use whatever system tables that ORACLE use to store the foreign key constraints to get the "children" of a table and then build a dynamic SQL to loop through and print the records. You can probably use PRO*C or other language that allows you to build your SQL before executing it. This, however, will have to be done BEFORE you delete the record(s). I don't think PL/SQL will do the job as it has a limited amount of buffer space. You will have to figure out how to best implement this scheme and when as it can be pretty messy.

Method 2:
Mark the records as deleted and then create a batch program to print out all deleted records at certain time (for example, every night). You may have to modify your programs not to show deleted records, though.

Method 3:
Create a mirror of all your tables. Create "pre-delete" triggers for each of the tables to copy the deleted rows into those mirror tables and then run a batch program to print and flush those records at appropriate time.

Method 4:
Similar to method 3 except that you create one or more generic table(s) with enough columns to store the record from the table with the most columns. You will want to add the name of the table the record come from to facilitate reporting.

Note: You may want to mix and match the above methods.

Have fun,
Kim Ng Received on Wed Nov 29 1995 - 00:00:00 CET