Re: mutating table error

I do not know why Oracle does not allow it, but i know the workaround:

Since you can get that nasty message only on a row level trigger you may do the following:

1. Create a package (pk_mut for example) with pl/sql table to store rowid's tROWID for example
2. Write a BEFORE UPDATE trigger (statement level) that should initialize that PL/SQL table:

pk_mut.tROWID.delete (7.3 or later)

3. Write an AFTER UPDATE trigger (row level) to store the rowid's of updated rows in that PL/SQL table

4. Write an AFTER UPDATE trigger (statement level) that will loop through PL/SQL table and do whatever you want. At this point you CAN select from the table already.

5. If you still have questions e-mail me directly at meltser_at_aigfpc.com

Bram Stieperaere wrote in message
<01bdc212$16672420$c93232c1_at_bse.sodexho-pass.be>...
>Is it possible to write an update trigger on a table with a select on this
>table in it?
>When I try I get a mutating table error.
>
>e.g. a trigger to prevent a table from having more than 1 entry for a
>certain value of an attribute
>
>Select count(*)
> into iCount
> from T
> where :old.attrib = <somevalue>
>;
>if icount > 0 then
> raise .... etc..
>
>Why doesn't Oracle allow this? is there a workaround?
Received on Sun Aug 09 1998 - 11:53:24 CDT