|
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
, schedtour s2
, guide g
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> until <s1.return_date.value>, and to
<s2.destination.value>, from <s2.start_date.value> until <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 until June 1st 2000, and to
INDONESIA, from May 28th 2000 until 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).
See also
Constraint messages
|
