From: Neil Saunders <n.j.saunders@gmail.com>
To: pgsql-sql@postgresql.org
Subject: Triggers & Conditional Assignment
Date: Thu, 15 Sep 2005 10:25:19 +0100
Message-ID: <ddcd549e0509150225586b1d06@mail.gmail.com> (raw)
Hi,
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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: n.j.saunders@gmail.com
Subject: Re: Triggers & Conditional Assignment
In-Reply-To: <ddcd549e0509150225586b1d06@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox