Need help with constraints in Oracle 7 (desperate)

Dear fellow Oracle programmers,

I am trying to implement a schema in Oracle 7 that was created using Logical Database Design implemented via Entity Relationship Diagrams. This has let me to a number of tables, each table containing a primary key, and existence dependent columns from other tables referencing keys in other tables (foreign keys).

Okay, so let's say I have

create table department (

	name	varchar2(20)	primary key,
	address	varchar2(30),
	...

create table account (

	number	number(3)	primary key,
	name	varchar2(20)	constraint fk_account
					references department(name)
					on delete cascades,
	...

Now, Oracle 7 behaves exactly as I would want on insert and deletes. I.e., checks for existance of parent record in department when a new record is added to account, deletes child records from account when parent record in department is deleted.

The problems start when trying to update the column department.name. I can't seem to come up with any combination of triggers that will cleanly allow me to change the value in department.name that don't

1. cause mutations in the trigger
2. violate the fk_account integrity constraint

if I disable the integrity constraint during the update on department, then the possibility exists that someone else using the database might insert or update a record such that the integrity constraint is violated during the other transaction update of department. I tried table locking, etc, but can't seem to get the update of department.name to execute the necessary commands in an atomic operation.

At this point, I'm very frustrated, 'cause I think that this operation should be very simple, and I can't believe Oracle 7 does not make simple provisions for it.

I really don't want to change the LDD of my database, just to accomodate Oracle 7. I'm hoping that some Oracle 7 programmer out there who knows more about Oracle than I do can give me an answer on how to implement these integrity constraints on the database, without imposing rules saying that primary or referenced columns cannot be updated.

Please, anyone, any ideas? Please let me know if you need more information, etc.

Please EMAIL all responses to reduce network traffic, I will post a followup summary if I get any good responses.

Thanks for your time,

--eric <eric_douglas_at_csufresno.edu>

-- 
/****************************************************************************


* Eric W. Douglas           Internet: eric_douglas_at_csufresno.edu

* CCMS/CSci, California     Voice: +1 209 278 3923 or +1 209 897 4556


* State University Fresno   Logic joke: if p->q and not q then not p, NOT!