Re: Got those old Mutation Blues

On 18 Nov 2001 17:03:45 -0800, rshea_at_my-deja.com (Richard Shea) wrote:

>Hi - I've got a table that looks a bit like this ...
>
>-- where entryid is a PK
>entryid NUMBER
>personid NUMBER,
>startdatetime DATE,
>enddatetime DATE
>
>... if you think of each record as a diary entry for an individual
>that would be a good model.
>
>I'd like to ensure that no person allocates the same period in the day
>twice. I tried to enforce this via a row level trigger (by doing a
>SELECT on the table for records belonging to the same person covering
>the same time period)and I got a mutation error when I do an update so
>I read ...
>
>http://govt.oracle.com/~tkyte/Mutate/index.html
>
>... plus the technet link and now understand a little more. BUT ! My
>question is this.
>
>If in the trigger I explicitly avoid selecting the record I'm changing
>(by having a 'entryid != :new.entryid') why does that still provoke a
>mutation error ? I mean surely it can be determined that I'm not
>involving any record that's changing ? Or to put it another way that
>I'm happy to ignore the record that is changing ?
>
>I'm not just philosphising here I was hoping someone could confirm
>that even using a 'entryid != :new.entryid' type clause clause I
>should still be getting a mutation error ? I'm wondering if I've made
>mistake elsewhere.
>
>any help would be appreciated.
>
>richard shea
>rshea_at_my-deja.com

You are selecting from the table you are mutating. What you are selecting doesn't matter at all, you are selecting. So you get the mutating table error.
From your problem description it looks like either your PK is incorrect, or you are trying to simulate the unique constraint. In that case you'd better impose one (on personid, startdatetime, enddatetime).

Hth

Sybrand Bakker, Senior Oracle DBA

To reply remove -verwijderdit from my e-mail address Received on Mon Nov 19 2001 - 00:05:40 CST