Variable Constraint Messages: Example 2
In the Travel Agency, the business rule: "A guide cannot guide more than one tour at the same time" is implemented by a restrictive, multi-record constraint:
SELECT g.first_name First, g.family_name Last,
s1.destination, s1.start_date, s1.return_date,
s2.destination, s2.start_date, s2.return_date
FROM schedtour s1
WHERE s1.guide = s2.guide
AND s1.guide = g.person_id
AND s1.schedtour_id != s2.schedtour_id
AND s1.start_date BETWEEN s2.start_date AND s2.return_date
Because of the columns specified in the SELECT clause, you can specify the message:
<First.value> <Last.value> currently guides the scheduled tours to:
<s1.destination.value>, from <s1.start_date.value> till <s1.return_date.value>, and to
<s2.destination.value>, from <s2.start_date.value> till <s2.return_date.value>.
These scheduled tours coincide. Please decide what to do.
The error message displayed might be:
"John Smith currently guides the scheduled tours to:
AUSTRALIA, from May 7th 2000 till June 1st 2000, and to
INDONESIA, from May 28th 2000 till Jun 21st 2000.
These scheduled tours coincide. Please decide what to do".
You can also use variable constraint messages in corrective constraints. For UPDATE constraints you can refer to columns mentioned in the SET clause and in the SELECT clause (if any).