|Subject:||Re: diary constraints|
|Views:||Raw Message | Whole Thread | Download mbox | Resend email|
I've actually just done the same thing - but for renting property. I've implemented the constraint as a trigger (Before insert/update, for each row), that first checks if the start_date is < end_date, and then performs a select on the bookings table using the OVERLAPS function. If there are more than 0 records returned, an exception is raised.
I've included the code below. You shouldn't need too many changes to adapt it to your needs!
/* This trigger function is responsible for ensuring temporal integrity
within the calendar_entries table (And it's children). It ensures that
only entries with no overlapping entries. */
/* First, check that the start_date > end_date */
IF NEW.start_date > NEW.end_date THEN
RAISE EXCEPTION 'ERROR: start_date must not be greater than end_date';
IF EXISTS (
WHERE ((start_date,end_date) OVERLAPS (NEW.start_date, NEW.end_date))
AND property_id = NEW.property_id LIMIT 1
RAISE EXCEPTION 'cannot add booking - overlapping calendar entries detected';
This message is for the designated recipient only and may contain privileged, proprietary, or otherwise private information. If you have received it in error, please notify the sender immediately and delete the original. Any other use of the email by you is prohibited.
|Next Message||Michael Glaesemann||2005-08-23 08:58:13||Re: diary constraints|
|Previous Message||Gary Stainburn||2005-08-23 08:33:58||diary constraints|