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