Re: Trigger / mutating table

sybrandb <sybrandb_at_gmail.com> said:
>On May 3, 11:37 am, Juha Laiho <Juha.Laiho_at_iki.fi> wrote:
>> I'm facing the ORA-04091 with a trigger I'm trying to write.
...
>> 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).

>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

Thanks, found it;

I had already read http://asktom.oracle.com/tkyte/Mutate/index.html, but didn't apparently read it well enough to get all the details. I now searched again, and found
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1500805205887 which is exactly what I was attempting to do. What I didn't get initially from the first article was the use of both row-level and statement-level triggers. Also the Databasejournal article http://www.databasejournal.com/features/oracle/article.php/3329121 helped to understand how the statement- and row-level triggers behave differently.

So, once more, my thanks to all those who've helped me in finding the solution.

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