Deferred trigger ..?

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 Received on Tue Jul 12 2005 - 08:12:07 CDT