SEO

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

Randi W wrote:
> 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?

No, but what you can do is use a combination of triggers and a deferred
CHECK constraint. For example, you could add a column called
NumCurrentAddresses to the Persons table, with a deferred CHECK
constraint to ensure it is <= 1. Then use triggers on the Address
tables to update the new column after inserts, updates and deletes.

Another possible approach is to create a materialized view that selects
only the Address rows where IsCurrent=1, and then add a deferred unique
constraint on the MV's person_id column.