When implementing multi-row constraints, it is important to first get the specificiation of the constraint absolutely clear.
The OP specified it as follows "...enforce the restriction that total participation within one agreement must sum to 100 percent.."
There is actually some ambiguity in this specification.
So what is meant, this (what I first thought was the case)?
create or replace assertion sum_pct_is_100 as
check(not exists
(select 'participation not summing up to 100%'
from (select p.agreement_id, sum(p.participation_pct) as sum_pct
from participant p
group by p.agreement_id) x
where x.sum_pct <> 100));
Or this?
create or replace assertion sum_pct_is_100 as
check(not exists
(select 'an agreement with no 100% participation'
from agreement a
where 100 <> (select nvl(sum(p.participation_pct),0)
from participant p
where p.agreement_id = a.agreement_id)));
In other words is this a single-table rule, only involving PARTICIPANT, or is this a two-table rule involving both tables?
Put simply: do we allow AGREEMENTs with no PARTICIPANTS?
Tom's first response actually has a solution for both (almost...): the MV-solution (the first one) implements the former assertion.
The trigger-solution (the second one) seems to attempt to implement the latter assertion. But is not complete as it allows a new agreement with no participation, but does not allow an existing (100% participated) agreement to have its participation removed.
Stew's code also attempts to implement the former assertion (When matched it performs a DELETE when the percentage is near 0.
It is not complete though, since it disregards updates of the agreement_id FK column (which is involved in the assertion text), which 'switches' a participants' participation to another agreement.
This can easily be fixed in his code.
[side comment]
By the way, if the latter assertion was intended, then my preference would be to introduce two assertions: the former one that checks all existing participation sums up to 100 per agreement (single-table constraint).
And one stating that there must be some participation for each agreement (two-table constraint):
create or replace assertion no_agree_without_participation
check(not exists
(select 'an agreement without participation
from agreement a
where not exists
(select 'some participation'
from participant p
where p.agreement_id = a.agreement_id)));
Allthough this is not the case for this particular example, in general, implementing an assertion that's actually a logical conjunction of two assertions, as the two seperate assertions, benefits serializability.
[end-of side comment]
With regards to my earlier comment:
By 'clever use of dbms_lock', I meant no magic other than that instead of relying on row-level locks, we can acquire an application lock (DBMS_LOCK) to ensure minimal serialization.
In order to "serialize at the agreement level" (the "clever" bit), the application lock's name would just have to involve the AGREEMENT_ID value.
Say we call this constraint SUMPCT100, then we can introduce an application lock named SUMPCT100[1] to serialize for agreement 1, and SUMPCT100[2], for agreement 2, etc.
And yes it then requires a range scan of the participant table as Tom followed up.
Here's an outline of the code:
- An after row trigger maintains which agreement_id's have been affected and require constraint validation.
- As this assertion is vulnerable for inserts, updates and deletes, it does so during execution of all three types of DML-statements.
- For updates only if one of the two involved columns was changed.
- These agreement_id values can be maintained in a (to be created) global temporary table (GTT), or in a persistent packaged array variable.
The after statement trigger then:
- Runs the check for each distinct agreement_id value maintained by the the row-trigger (it does the range-scan and the SUM).
- But before doing so, it acquires the application lock described above.
- Since this rule requires a deferred execution model, when a violation is detected, instead of doing a raise_application_error, the statement trigger saves this violation.
- Violations are saved in another (to be created) GTT, say GTT2.
- As part of validating the constraint, the statement trigger also removes prior saved violations that are 'ok' now.
- This way whenever GTT2 still has contents, the transaction should not be allowed to commit.
- To ensure this, a simple 'select * from GTT2' materialized view is created (on commit refresh)
- So at commit we only do a quick check to see if any violations are still present, i.e. we do not perform any (potentially) complex and time-consuming constraint validation.
- And an after insert trigger on this MV will raise the error(s) when GTT2 is not empty.
Here's an example of a transaction:
SQL> insert into agreements values(1,'Agree 1');
1 row created.
SQL> insert into agreements values(2,'Agree 2');
1 row created.
SQL> insert into agreements values(3,'Agree 3');
1 row created.
SQL>
SQL>
SQL> insert into participant values(10,1,100);
1 row created.
SQL> --
SQL> -- after-statement trigger acquired application lock sum_pct_is_100[1], validated agreement 1, found it to be ok.
SQL> --
SQL>
SQL> insert into participant values(10,2,90);
1 row created.
SQL> --
SQL> -- after-statement trigger acquired application lock sum_pct_is_100[2], validated agreement 2, detected violation, saved it in a GTT.
SQL> --
SQL>
SQL> insert into participant values(10,3,50);
1 row created.
SQL> --
SQL> -- after-statement trigger acquired application lock sum_pct_is_100[3], validated agreement 3, detected violation, saved it in a GTT.
SQL> --
SQL>
SQL> commit;
commit
*
ERROR at line 1:
ORA-12008: error in materialized view refresh path
ORA-20999: Sum of participation for agreement 3 should be 100 (50).
ORA-06512: at "TOON.SP_PRE_COMMIT_CHECK", line 26
ORA-06512: at line 1
ORA-06512: at "TOON.SP_PRE_COMMIT_CHECK", line 23
ORA-20999: Sum of participation for agreement 2 should be 100 (90).
ORA-06512: at "TOON.MV_PRE_COMMIT_TRIGGER", line 3
ORA-04088: error during execution of trigger 'TOON.MV_PRE_COMMIT_TRIGGER'
SQL> --
SQL> -- MV (select * from GTT), gets populated, insert-trigger on MV reports errors.
SQL> -- Transaction gets rolled back, application locks get released.
SQL> --
This (deferred) execution model seems rather elaborate, but I believe it is no different than what takes place 'under-the-covers' when Oracle validates a deferred foreign key constraint.
That is:
- locking is always immediate.
- intermediate violations are temporarily saved and rechecked on subsequent DML-statements inside the transaction.
And of course :-) ... all this code can be generated, the essence of the constraint at hand is:
- It can be validated at the agreement-id level (and hence should be serialized at this level).
- It should be validated on PARTICIPANT inserts, deletes, and updates of agreement_id and participation_pct.
- The way to validate it is to run:
select 'error'
from (select nvl(sum(participation_pct),0) as pct
from participant
where agreement_id = :b1)
where pct <> 100;
With RuleGen (www.rulegen.com) you only input the above essence, and then all code is generated for you.
One final comment:
To minimize the row-by-row constraint validating, one should of course try to use multi-row DML statements as-much-as possible.
So if a user has a UI in which he/she can enter the participants and participation percentages for a given agreement, then ideally all this UI-data would get transferred into the DBMS via a stored-procedure call.
Then inside the stored procedure one should intelligently compose one multi-row insert statement to insert all participations in one go.
That way the statement trigger only fires once.