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

I am designing a DB where column/table constraints are not sufficient
for data integrity (e.g., guaranteeing non-recursive tree graphs) so
I'm writing my first complicated triggers and I have been searching
docs and archives of this list for detailed information on when
triggers are fired relative to constraints created with CREATE TABLE
and/or ALTER TABLE.


For example, in what phase are CHECK and FOREIGN KEY constraints (as
well as NOT NULL and UNIQUE constraints) checked relative to the order
of triggers firing.  Documentation clearly outlines custom trigger
order as:

  1....before-statement

  2....before-row
      [before-row ...]

  ------data now visible------

  3....after-row
      [after-row ...]

  4....after-statement

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).  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).

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.

Specific #1: Is there a way to control the order of column/table
constraints relative to custom triggers?

Specific #2: If, say, CHECK and FOREIGN KEY constraints are checked in
the after-row phase (or just before it, but after the before-row
phase), so I need to move my custom constraint logic to the after-row
phase to take advantage of these constraints is the only way for me to
abort the insert or update by raising an error?  (If the my logic is
in the before-row phase, which is an intuitive place to put it, I
could just return null, but that won't work in the after-row phase.)


BTW, if I can get decent info, I'd be willing to volunteer to
contribute well written documentation to give back to the cause.


Thanks,

Dan

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