Re: Ref. Integrity: Restricted/cascaded DELETE

Janet Traub (trau2349_at_hgc.edu) wrote:
: Hello folks, I could use some Ref. integrity advice.
 

: I want to set up a ref. integrity rule at the database level
: for a particular parent key that is a foreign key to 15 other tables.
 

: My business rule is:
 

: Allow either a restricted delete or cascaded delete depending on the type
: of user accessing the database. If the user is an "administrator" set
: ON DELETE CASCADE. For all other users, set restricted delete
: (i.e. only allow deletion if none of the 15 tables contains a
: dependent child row).
 

: My question:
 

: What is the best way define this fexibilty?
: Should I create 2 named constraints in each of the 15 tables, such as:
 

: CREATE TABLE matter_status (
: .
: .
: CONSTRAINT fk_matter_no FOREIGN KEY matter_no REFERENCES matter_info,
: CONSTRAINT fk2_matter_no FOREIGN KEY matter_no REFERENCES
: matter_info ON DELETE CASCADE);
 

: and then create a Stored procedure (or trigger) that checks to see if the
: user is an Admin. and if so, DISABLEs the restricted constraint for each of the
: 15 tables and ENABLEs the cascaded constraint for each table?
 

: CREATE TRIGGER delete_matter (user_type IN VARCHAR2)
: BEFORE DELETE ON matter_info
: BEFORE
: if user_type is 'ADM' then
: ALTER TABLE matter_contact DISABLE fk_...
: ALTER TABLE matter_status DISABLE fk_...
: .
: .
: ALTER TABLE matter_contact ENABLE fk2_..
: ALTER TABLE matter_status ENABLE fk2_..
: .
: .
: END
 
: Something tells me there's probably a simpler way.

I don't think the alter table statements are a good idea, since alter table will force a commit.
I would write a stored procedure, give users a select grant on the table but not the delete grant and grant the procedure to the users.

Teijo Doornkamp (doornkampt_at_vertis.nl) Received on Fri Nov 25 1994 - 01:05:58 CET