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

Write two triggers: one After delete for each row, and one after delete. Both call procedures in a package. Put the processed id's (your complete primary key!), using the for each row trigger in an array (defined within the package).
Then, from the after statement trigger, process all id's in the array - by then, the table is not mutating anymore.

This is a standard approach.

On the other hand, what's the relation between those special ID's in table A, the deleted row, and table B? Can't you use the WHEN clause (WHEN :old.ID is not null)

-- 
Regards,
Frank van Bortel