Re: Q on mutating errors, before/after triggers, and an example

Simon Goland wrote:

> table person with the following fields:
> person_id (ID)
> address (some address)
> default_address (is the address default for mailing, Y/N)
>
> A person can have many addresses, but either 0 or 1 default_address,
> i.e. there can be at most 1 record for a specific person with 'Y' in the
> default_address column. There is a row-level, before trigger on
> insert/update to enforce it.
>
> When a row for some person_id is being updated from N to Y (for
> default_address flag), the trigger fires up. The way it was designed
> (and not by me) is that it calls a function that returns the number of
> rows for that very person_id that already have Y for the default_address
> flag. If the value returned is 0, the row can be updated. Otherwise it
> is an error.
>
> This is where the mutating error occurs, as I was told, and this is what
> confuses me. When the before trigger fires and the rows with Y are being
> counted, my logic tells me that the current row under consideration
> still has the old value of N. After all, the trigger fires up before the
> update is done. Yet the trigger seems to "see" it as changed already and
> returns an error.
>
> What am I missing? And what is a possible solution?

  The mutating error raises because you read the table that the trigger fires. It is not your logic fault. The possible solution is select count(*) from person where default_address = 'Y'. If it is equal to zero, you can insert it. Otherwise, you handle it in another way.

---
Name   : Lun Wing San
Title  : Oracle Application Developer of Hong Kong Productivity Council
         Oracle Database Administrator and System Administrator of QRC
Phone  : (852)27885841