| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: mutating table error
A copy of this was sent to "Bram Stieperaere" <bse_at_NO_SPAMsodexho-pass.be>
(if that email address didn't require changing)
On 7 Aug 1998 14:45:51 GMT, you wrote:
>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?
First off, I would use a constraint for this (UNIQUE on attrib). That will prevent you from having >1 entry for a certain value of an attribute and will work correctly (without writing code)...
Secondly, you can't really write the constraint you want to in a trigger (even if you didn't hit the mutating table). Oracle supports multi-versioning which means reads dont block. If 2 people inserted the same value at about the same time, each would read 'Zero rows' back out (unless you had the unique constraint which would prevent one of them from entering the value).
Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Herndon VA
http://govt.us.oracle.com/ -- downloadable utilities
Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Fri Aug 07 1998 - 10:07:03 CDT
 |
 |