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
>
>

What is the relation between 'myTable' and 'B'? Any chance that you have a foreign key myTable.id referencing b.bid ?

It sounds like you have a parent/child relationship and you don't want any parents without at least one child.

In that case you could submit a job that cleans out the parent. However, this will only be visible after a commit, so if your application must see the change immediately that's not viable.

The best way would have been if the only way to delete a row in myTable was through a package/stored procedure - that avoids the trigger problem altogether.

My 2c

Holger Received on Thu Mar 03 2005 - 07:44:13 CST