"remg" <remq75_at_wp.pl> wrote in message
news:d0730g$5h4$1_at_nemesis.news.tpi.pl...
> Hi everyone,
> I try to write trigger on Oracle 8.1.7. The trigger on table A should
delete
> records on table B only when table A doesn't contain any records with
> special ID.
>
> For example:
>
> CREATE OR REPLACE TRIGGER "myTrigger" AFTER
> DELETE
> ON "myTable"
> FOR EACH ROW
> declare cnt number;
> begin
> select count(distinct id) into cnt from myTable where myColumn =
> :new.myColumn;
> if (cnt=0) then
> delete from table B where .....
> end if;
> end;
>
> Unfortunatelly, I get an error "table is mutating, trigger may not see
it..
> " which is obvious for me, because trigger tries to count records from
table
> on which it is based.
> Is there any way to solve that problem? How to write this "conditional"
> trigger? The trigger must be "for each row".
> Regards,
> remg
>
>
One piece of advice is to not have case sensitive object names. It is going
to complicate a lot of things. Don't create objects with "'s. As others
have said you might be looking for a foreign key relationship.(delete
cascade). Else you would have to use a mutating trigger wor around see
asktom.oracle.com.
Jim
Received on Thu Mar 03 2005 - 09:48:46 CST
