Variable Constraint Messages: Example 2

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).