Transitional Constraints

Transitional constraints are constraints that, in their definition, refer to both the old values and the new values of one or more of the columns manipulated.

These constraints contain the OLD() function, or (for table constraints) have transition properties, or both. Transition properties are Transition Table, Alias, Fire on Insert, Fire on Delete and Fire on Update.

Example 1

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. Therefore, you need to define a transitional constraint. Most likely, this will be a transitional domain constraint with condition:

DOMAIN > OLD(DOMAIN)+10

Example 2

A Travel Agency business rule is that you can only cancel an existing reservation. In other words, the cancel date of a reservation may not be entered when the reservation is first entered, only in a subsequent session. The transitional, restrictive single-record constraint reads:

SELECT '' violation

FROM reservation

WHERE cancel_date IS NOT NULL

AND OLD(res_id) IS NULL

Referring to the old state of the reservation record can only check this rule. When a record is first inserted, the old value of all primary key columns is NULL. Therefore the user is not allowed to enter a cancel_date if the old value is NULL, but only if it is not NULL (that is, a previously entered reservation).