Re: Trigger problem

remg wrote:
> 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

Goto http://tahiti.oracle.com and look up usage for AUTONOMOUS_TRANSACTION pragma.

Regards
/Rauf Received on Thu Mar 03 2005 - 08:01:37 CST