Hello,
We are using Oracle 8.1.7.
I have a table of adresses where there will be several rows for each person.
Each row has a column 'isCurrent', and when the value of this column =1 it
means that this is the address where the person stays right now.

It would be nice to find some way in Oracle to ensure database integrity so
that one and only one address for a person has isCurrent=1. I think that
this is too complex to be done by check constraints, so I have to write
triggers. I am aware of the 'mutating'-error I could get, and guess I will
have to write three triggers:

1. before-statement-trigger on insert and update (no delete will be done):
clear a PL/SQL table
2. after-row on insert and update:
add rowid for this row to PL/SQL table
3. after-statement-trigger on insert and update
perform check and raise error if number of rows for this person with
isCurrent=1 is zero or more than one.

But - the application using the database already has some logic that might
perform an UPDATE to set the old current address to isCurrent=0 and an
INSERT that inserts a new current address. Some times there might be two
updates, setting an older address current, and the current to not-current.

These will be treated as two statements, so what I would really like is if
the after-statment-trigger could be fired at COMMIT time. A similar thing
can be done by using 'deferred'-setting for constraints.

Is it possible?

Thanks,
Randi W