Restrictive Multi-Record Constraints

See Also

Example

In the Travel Agency, the number of reservations for a tour cannot be overbooked by more than 20%. Once the maximum number of overbookings has been reached, no more reservations can be made. The following constraint takes care of this:

SELECT    '' violation

FROM        schedtour s

WHERE    1.2 * s.max_part <

(

   SELECT    COUNT(*)

   FROM    reservation r

       , participant pt

   WHERE    r.schedtour_id = s.schedtour_id

   AND    pt.res_id = r.res_id

)

The Rules Engine evaluates this constraint whenever ANY of the columns it refers to is manipulated.

When a record involved in a restrictive multi-record constraint is stored, the Rules Engine creates a "violation list". You can look at this list by choosing File, Transactions from the menu bar. The Rules Engine checks these violations when the transaction is committed. If one of these (possible) violations is indeed found to occur (that is, the corresponding constraint is violated), the commit will fail and the error message associated with the constraint will appear.

OLD()

You can use the OLD() function, or you can set transition properties for the constraint, or both, in which case you turn the constraint into a transitional constraint.

Transitional constraints are fully handled when the record is stored (record validation), that is, they are not entered on the violation list to be checked at commit time. You may not use OLD() on more than one table. Transitional constraints are only evaluated if this table (the transition table) is manipulated, not if another table mentioned in the constraint SQL statements is manipulated.