On Mon, 2007-03-19 at 19:08 +0100, hubert depesz lubaczewski wrote:
> On 3/19/07, Jeff Davis <pgsql@j-davis.com> wrote:
> > You can use an AFTER trigger instead of a CHECK constraint (but that may
> > have a performance impact - test for your application).
>
> are you sure you meant AFTER? why? generally data-checks should be in
> before triggers. i guess.
>
If you do the check BEFORE, you have to make sure that no other BEFORE
triggers that execute afterward modify the data again.
Assuming your AFTER trigger is on INSERT and UPDATE, there is no way for
a subsequent AFTER trigger to modify the data to be invalid. So an AFTER
trigger is more of an assurance that your data is valid.
Note that AFTER triggers need to queue up, so if you do a huge update
and have an AFTER trigger, it might use a lot of memory. BEFORE triggers
don't have that problem. If you're very concerned about this you could
use a BEFORE trigger and just make sure that no other trigger will cause
a problem.
Regards,
Jeff Davis