Re: Own messages for constraints?

On Mon, 2007-03-19 at 19:26 +0100, hubert depesz lubaczewski wrote:
> On 3/19/07, Martijn van Oosterhout <kleptog(at)svana(dot)org> wrote:
> > In an AFTER trigger you can be sure you're seeing what actually got
> > inserted. In a BEFORE trigger other triggers after you could still
> > modify the data...
>
> yes but in after trigger the only thing you can do is to raise
> exception. you cannot fix the data, issue warning, or simply stop the
> insert/update without breaking the transaction.
>

If you only issue a warning, it's not a constraint because data
violating the constraint still goes in. And you can issue a warning in
an AFTER trigger.

Fixing the data is probably something that should be done in a different
place (like the application correcting the data). It also begs the
question: If the data can be fixed, why is the original form not
acceptable anyway (i.e. fixed in the datatype's input function)?

I assume by "stop the insert/update without breaking the transaction"
you mean a return NULL from the BEFORE trigger, thereby not inserting
the row. COMMIT should mean "yes, I successfully completed what you
asked," and that usually means that the data was actually inserted.

You're correct that you have more flexibility with a BEFORE trigger in
many ways. However, be careful using those strategies to constrain data.
Generally you do want it to break the transaction if the data you're
trying to insert is invalid.

Regards,
Jeff Davis