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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
