Re: Delete trigger problem

Here's a possibility, but I don't know about performance on large tables. This assumes that the tables have a true one-to-one mandatory relationship, in other words, that there must be a record in table A for each record in table B. If you allow records in table B without corresponding records in table A, those table B records would get deleted by this trigger.

create or replace trigger my_trigger
after delete on tab_a

begin
  delete from tab_b b
  where b.col_1 not in
  (select a.col_1 from tab_a a);
end;

Larry

In article <s36tgqm7hsq81_at_corp.supernews.com>,   "Thomas Ruschak" <ruschak_at_wintek.com> wrote:
> 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
>
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Fri Nov 19 1999 - 14:42:41 CST