cascading delete while keeping "glue record" safe

        i've got a data management system where a bunch of users login to a middleware product which then connects to oracle and conducts certain transactions on their behalf. since all transactions thus appear to have come from the user as which the middleware product logs in as far as oracle is concerned, i've taken to storing information about the middleware product's users in another oracle table, adding fields to each table for created_by, created_date, modified_by, and modified_date, and having the middleware populate those fields as a primitive sort of accounting. my current project, however, involves building a personnel database that can be updated by the same people that it describes; rather than setting up a separate table of middleware users, then, i just made those accounting fields keyed to the person_info table that most of the rest of the db revolves around. for the initial load of data, i created as the very first record in any table a "glue record" in the person_info table, whose creator is listed as itself; the sqlload batches we are using to seed the database then all use that glue record as their creator, since the accounting fields are required.

        i've now been informed, however, that our middleware product needs to allow people to do cascading deletes on data, which raises an interesting problem for me: the "proper" way to do this, obviously, would be to alter the relevant tables with the "on delete cascade" constraint; one of the tables thus constrained, however, would be the person_info table. as i understand the cascading delete, then, that glue record suddenly becomes the end-all be-all of the entire database; since every other record in every other table references that record, deleting it will basically empty out the entire db.

        one solution that i've thought of is to create a trigger as follows:

create trigger save_person_info_glue_record before delete on PERSON_INFO
for each row
declare

   glue_user_exception EXCEPTION
begin

   IF person_id='1' THEN

      RAISE glue_user_exception
   END IF;
EXCEPTION
   WHEN glue_user_exception THEN

      RAISE_APPLICATION_ERROR (-20001,
         'Cannot delete Glue Record with person_id = 1');

        will this actually prevent the glue record (with person_id=1) from ever being deleted (short of dropping the tables), or is there some way around it?

        also, are there any better ways to avoid this situation? is anyone else doing this sort of pseudo-accounting, and if so, do they do it any differently?

	tia,
	sweth.

--
Sweth Chandramouli
IS Coordinator, The George Washington University <sweth_at_gwu.edu> / (202) 994 - 8521 (V) / (202) 994 - 0458 (F) <a href="http://astaroth.nit.gwu.edu/~sweth/disc.html">*</a> Received on Tue May 25 1999 - 18:36:01 CDT