Thread: 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