Обсуждение: Right way to reject INSERTs and UPDATEs

Hello!
I have postgresql table that stores dome date range: it has range-begin
and range-end columns of type date. I want to enforce that 1) range-begin
would always before range-end and 2) there would be no range overlaps.

First problem could be solved with CHECK constraint. The only possibility
to solve second problem is to use a trigger. Trigger would fire
BEFORE INSERT OR UPDATE and FOR EACH ROW and invoke pgplsql function
that returns NULL or "new" depending on condition met or not;

These solution work and enforce data integrity as needed. Sadly, database
interface I use (Trolltech Qt 3) can't track when trigger aborts
update or insert. Can I raise exception in trigger or something?

--
A: Because it messes up the order in which people normally read text.
Q: Why is top-posting such a bad thing?
A: Top-posting.
Q: What is the most annoying thing in e-mail?

Dmitry Teslenko wrote:
> These solution work and enforce data integrity as needed. Sadly, database
> interface I use (Trolltech Qt 3) can't track when trigger aborts
> update or insert. Can I raise exception in trigger or something?

RAISE EXCEPTION 'Failed to update mytable: date range (%,%) overlaps an
existing entry', NEW.from_dt, NEW.to_dt;

You can also do RAISE NOTICE, WARNING etc. See the pl/pgsql section of
the manual for details.

--
   Richard Huxton
   Archonet Ltd

On Tue, Jul 29, 2008 at 02:25:21PM +0400, Dmitry Teslenko wrote:
> These solution work and enforce data integrity as needed. Sadly, database
> interface I use (Trolltech Qt 3) can't track when trigger aborts
> update or insert. Can I raise exception in trigger or something?

of course. syntax:
RAISE EXCEPTION 'description, with param %', some_param;

depesz

--
Linked in: http://www.linkedin.com/in/depesz
jid/gtalk: depesz@depesz.com
aim:       depeszhdl
skype:     depesz_hdl