Transitional constraints

A transitional constraint is a constraint that, in its definition, refers to both the old values and the new values of one or more of the columns manipulated.

A transitional constraint contains the OLD() function, or (if a table constraint) has transition properties, or both. Transition properties are: Transition Table, Alias, Fire on Insert, Fire on Delete and Fire on Update.

Example 1 - Transitional domain constraint

A Travel Agency business rule is that discount percentages cannot be increased by more than 10 (that is, 10 %) at a time. To check this rule, you need to compare the old and new values for the discount percentage:

DOMAIN > OLD(DOMAIN) + 10

Example 2 - Transitional single-record constraint

A Travel Agency business rule is that you can only cancel an already existing reservation. In other words, the cancel date of a reservation may not be entered when the reservation first appears in the system, only in a subsequent phase. You can only check this by referring to the old state of the reservation record. When a record is first inserted, the old value of all primary key columns is NULL. The user is not allowed to enter a cancel_date if the old RES_ID primary key value is NULL:

SELECT    '' violation
FROM      reservation
WHERE     cancel_date IS NOT NULL
AND       OLD( res_id ) IS NULL

This is a transitional, restrictive single-record constraint.

Example 3 - Transitional multi-record constraint

A Travel Agency calculates a group discount percentage based on the number of participants in the reservation. It has a business rule saying that this percentage is not allowed to change at a later time:

UPDATE     reservation r
SET        r.group_discount =
(
     SELECT   d.percentage
     FROM     group_discount d
     WHERE    r.num_part BETWEEN d.min_part AND d.max_part
)
WHERE      OLD( r.res_id ) IS NULL

This is a transitional, corrective multi-record constraint. It involves 2 tables. The table that has the OLD() function is the transition table. A constraint can have at most 1 transition table. The constraint is evaluated only when the transition table is manipulated.