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
