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

I am looking for suggestions, opinions, and maybe sources of reading information on this "famous" issue. I will start with a short example, simplified for this purpose:

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?

Thanks,

-- 


[ Simon Goland            sg_at_mda.ca ]

[                B-)>               ]

[ Without action there is no change ]