Local Evaluation of Changed Record
When a manipulation takes place on columns mentioned in multi-record constraints, the Rules Engine adds violation entries to the list of constraints to be evaluated at commit time.
Before handling a multi-record constraint, the Rules Engine first checks if locally stored column values can be used.
Consider the following constraint:
SELECT '' violation
FROM SCHEDTOUR st
( SELECT ''
FROM PARTICIPANT p
WHERE p.res_id = r.res_id
AND r.schedtour_id = st.schedtour_id
AND p.name = 'JOHNSON')
From the last WHERE clause in the subquery, it is clear that the rule can only ever be violated if a participant with name JOHNSON is inserted, or if a participant's name is updated to JOHNSON.
If locally stored values allow the Rules Engine to exclude these possibilities, the rule is no longer shortlisted for evaluation at commit time. If a user books a person with name LEWIS for a tour, this clearly cannot lead to a violation.
This strategy will result in fewer statements being sent to the RDBMS at commit time. It can be used not only for constraints with hard-coded values, such as this one, but also in all cases where a subquery can be substituted by a constant by the run-time local evaluator.