Got those old Mutation Blues

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 Received on Sun Nov 18 2001 - 19:03:45 CST