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 first appears in the system, only in a subsequent session. 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.