Delete trigger problem

    I'm having some trouble with a delete trigger... I have two tables, A & B with a one-to-one relationship. There is a foreign key in table A that holds a pk from table B. Simple.

    Now, the problem. I want to ensure that if I delete a record in A, the record in B gets deleted. I know that if the foreign key was in B, I could use a cascade delete to accomplish it, right? Whenever I deleted an A, the B record that was related to it would be deleted by the cascade.

    Unfortunately, however, I need the foreign key to be in A. So, I figure I'll write a delete trigger for table A that'll delete the record in B. Unfortunately, I run into the Oracle "mutating record" error message. I can't do something like

    delete b where pk_b = a.fk_b

    Since A is mutating at the time, I can't get access to the value of a.fk_b. Is there any way around this that can be handled strictly through triggers? I don't want the program functions that are deleting records in table A to have to jump through any hoops, but I can't see a way to do it nicely.

    Any solutions or help would be rewarded with obsequious fawning & slavish devotion :-)

TIA,
Thomas Ruschak Received on Wed Nov 17 1999 - 21:44:36 CST