Re: order of trigger firing relative to column/table constraints

On 18/12/2009 1:18 PM, Daniel Popowich wrote:

> For example, it was at first surprising to discover in my before-row
> trigger that foreign key constraints had not yet been checked (which
> I assumed I could take for granted in my trigger since I had defined
> the column constraint).

Nope. CHECK constraints and NOT NULL constraints aren't tested either.
After all, you might want to have the client leave those fields null (or
even force them to leave them null/default using column permissions) and
then populate them from your trigger.

Examples include `inserted by user', `last modified by user' etc
columns, where you don't want the user to have the ability to set or
alter them, so you might GRANT them permission to modify all columns
except those ones, and set those columns from a trigger.

> Which means the foreign key constraint
> checking will have to be done twice: once in my custom trigger in the
> before-row phase (because my logic requires it there) and again when
> the foreign key column constraint is checked (whenever that is).

... unless you can move your logic to an AFTER trigger. You can still
roll back the change by throwing an exception.

AFTER is a better place to do this sort of thing anyway, really. Your
BEFORE triggers might modify the data in ways that change the results of
your checks, so they're quite dependent on trigger firing order. Much
safer to put it in AFTER, though you do incur the overhead of doing the
work and rolling it back that way.

> In summary, I have one general question and two specific questions:
>
> General: is it documented somewhere in any detail the order of
> column/table constraint checking relative to custom triggers.

IIRC, fkey checks are just AFTER ... FOR EACH ROW triggers. I *think*
they're just fired in alphabetical order along with the other triggers,
but I'm not sure what name they have - if it's the fkey constraint name,
or something else.

> abort the insert or update by raising an error?

Sure. RAISE an exception. I think that's basically how the fkey checks
do it.

--
Craig Ringer