Transitional Constraint Examples

See Also

Example 1

To check that a column value does not increase by more than 10 at a time, you can write a domain constraint. In the Do Not Allow That field of the constraint, you would enter:

DOMAIN > OLD(DOMAIN) + 10

Example 2

To check if a cancel date is left empty when a reservation is inserted, define this constraint:

SELECT  ' ' violation

FROM    reservation

WHERE   cancel_date IS NOT NULL

and set the Transition properties to:

Transition Table:  RESERVATION

Fire On Insert:    Always

Fire On Delete:    Never

Fire On Update:    Never

However, you can also express the rule simply by writing the constraint SQL statements:

SELECT ' ' violation

FROM   reservation

WHERE  cancel_date IS NOT NULL

AND    OLD(res_id) IS NULL

where res_id is the primary key column. Therefore the last WHERE clause implies that the record is inserted.

Because the OLD() function appears in your statement, the constraint checker automatically sets transition properties when you have the constraint checked. You are free to reset these properties later.