Re: Trigger / mutating table

On May 3, 11:37 am, Juha Laiho <Juha.La..._at_iki.fi> wrote:
> Hello,
>
> I'm facing the ORA-04091 with a trigger I'm trying to write.
> After reading Google on the topic, I now understand that it is
> not allowed to have the trigger code refer to the table from
> which the trigger was activated.
>
> So, I'm facing a design issue here, and would appreciate any
> help. Simplified, I'm having two tables, parent and subsidiary.
> Each row of subsidiary refers to a row in parent (many-to-one;
> there can be several subsidiary rows referring to a single parent row).
>
> What I attempt to do with the trigger is to remove the parent row
> when there are no more subsidiary rows pointing to it (so, when
> subsidiary is removed, check which was the parent for this subsidiary,
> and are there any subsidiaries left with the same parent -- and if
> not, remove the parent along with the subsidiary).
>
> This doesn't sound like anything extraordinary or uncommon -- but
> I don't seem to find the correct pattern for implementing this.
> Any pointers for solutions?
>
> Thanks,
> --
> Wolf a.k.a. Juha Laiho Espoo, Finland
> (GC 3.0) GIT d- s+: a C++ ULSH++++$ P++@ L+++ E- W+$@ N++ !K w !O !M V
> PS(+) PE Y+ PGP(+) t- 5 !X R !tv b+ !DI D G e+ h---- r+++ y++++
> "...cancel my subscription to the resurrection!" (Jim Morrison)

Looks like

set up a before statement trigger, initializing a collection of id's after each row: buffer the subsidiary id after statement: loop through your collection, issue your select statement, and deal with the parent accordingly Classical solution , available on http://asktom.oracle.com

--
Sybrand Bakker
Senior Oracle DBA