I've run in to a small problem when writing a trigger.
For simplicities sake lets say that I have 2 tables – 'bookings' and 'unavailable_periods'. Both have columns 'start_date','end_date', and 'property_id'.
I have written a trigger that is fired on inserts and updates for both tables that simply ensures that no intervals (defined by start_date and end_date) overlap for the same property across both tables.
It works simply by doing a SELECT using the OVERLAP keyword on NEW.start_date , and NEW.end_date for both tables (Ignoring the record being modified). This works fine on inserts (Where both start_date and end_date are specified), and updates that modify both start_date and end_date, but for updates where I only update 'start_date', for example, the trigger fails because NEW.end_date is empty.
Whats the best way around this?
I've tried to write something along the lines of the following:
DECLARE sdate DATE; edate DATE;
BEGIN sdate = (NEW.start_date IS NOT NULL) ? NEW.start_date : OLD.start_date; edate = (NEW.end_date IS NOT NULL) ? NEW.end_date : OLD.end_date; …
But conditional assignment doesn't seem to be catered for. The next best thing is a series of IF THEN ELSIF ELSE statements to assign sdate and edate, or is there another technique that I've missed entirely?
Kind Regards,
Neil Saunders.
---------------------------(end of broadcast)--------------------------- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to majordomo@postgresql.org so that your message can get through to the mailing list cleanly
-- with regards, S.Gnanavel Satyam Computer Services Ltd.
