Update On Self Constraints

Within one row event, the Rules Engine attempts to handle corrective constraints pre-store, that is, before the actual storage of the record. This is quicker, it is easier to control, and open to further optimization strategies.

These constraints are handled as Update On Self constraints. They occur at record validation time, before the storage of the record.

Corrective constraints are handled as Update On Self constraints if they potentially update the current record. This is the case if the triggering table is the same as the updated table (See "Tables in Constraints").

A corrective constraint can be handled as an Update On Self if the following requirements are met:

·	The constraint SQL statement is an UPDATE statement.

·	The updated table is the same as the triggering table.

·	The constraint is not deferred to commit time.

·	The constraint does not contain an outer-join condition.

·	The constraint does not perform an UPDATE on a subtype indicator.

NOTE:

Transitional constraints, and therefore also transitional Update On Self constraints, are only activated by row events on the transition table, so in this case the triggering table not only is the same as the updated table, but also the same as the transition table.

Example

Consider the business rule: "The price of a reservation is the price of the scheduled tour multiplied by the number of participants in the reservation. Personal discount and group discount percentages may apply. If both apply, only the higher percentage is deduced." This rule is implemented by a corrective constraint:

UPDATE reservation r

SET r.price=

(SELECT s.price*COUNT(pt.person_id) *

((100 GREATEST(r.person_discount,r.group_discount))/100)

FROM schedtour s

,participant pt

WHERE s.schedtour_id=r.schedtour_id

AND r.res_id=pt.res_id

GROUP BY s.price,r.person_discount,r.group_discount)

The price calculation constraint is non-transitional. It will be evaluated as soon as one of the columns mentioned in it is manipulated.

If it is evaluated as a result of one of the RESERVATION columns being modified, the constraint can be handled as an Update On Self constraint. For instance, this occurs if the scheduled tour of an existing reservation is changed. As a result of this update, the price must be re-calculated.

This constraint can be handled as an Update On Self constraint because the two adjustments required (the update of the SCHEDTOUR_ID column value and the update of the PRICE column value) can be made within the same row event.

In this case, this is a multi-record constraint that is handled within a single row event.

If a participant is added to an existing reservation, this constraint must be handled as Reparation because the two adjustments required (the insert into PARTICIPANT and the update of the PRICE column value) cannot be made within the same row event.