Thread: before and after triggers
After reading the manual, this point didn't seem to have been made. Trigger
fucntions are supposed to return OPAQUE, (i.e. 'void' in 'C/++' syntax). But the
manuals also say that BEFORE triggers can return NULL to avoid the INSERT or
UPDATE from occurring. Is this contradictory? Is that actually ONE way to avoid
a UPDATE or INSERTION from happening?
My understanding, inferred different parts of diffeent manuals, is the below,
correct me if I'm wrong:
BEFORE TRIGGERS
Can change the values in the NEW Tuple for:
INSERTS and UPDATES.
Can void the action of:
INSERTS and UPDATES
by returning NULL. (does this kill the transaction?)
Can stop completely an action with an error message for:
INSERTS,DELETES, and UPDATES
by RAISING an EXCEPTION. (This DOES Kill the xaction)
AFTER TRIGGERS
Can stop completely an action with an error message:
INSERTS,DELETES, and UPDATES
by RAISING an EXCEPTION. (This DOES Kill the xaction)
Have the advantage of seeing the final results of
an action on a table before canceling it.
ALL TRIGGERS
Are not DEFERRABLE in anyway. They happen immediately:
AFTER or BEFORE each ROW is acted upon.
On Fri, 4 Apr 2003, Dennis Gearon wrote: > After reading the manual, this point didn't seem to have been made. Trigger > fucntions are supposed to return OPAQUE, (i.e. 'void' in 'C/++' syntax). But the That's not what opaque means, or more precisely not only what opaque means (it also is rows from triggers, internal values like C strings and probably some other things). In 7.4, triggers return trigger. > manuals also say that BEFORE triggers can return NULL to avoid the INSERT or > UPDATE from occurring. Is this contradictory? Is that actually ONE way to avoid > a UPDATE or INSERTION from happening? > > BEFORE TRIGGERS > Can change the values in the NEW Tuple for: > INSERTS and UPDATES. > Can void the action of: > INSERTS and UPDATES > by returning NULL. (does this kill the transaction?) The action is ignored (for this row), but it's not an error condition and doesn't kill the transaction. > ALL TRIGGERS > Are not DEFERRABLE in anyway. They happen immediately: > AFTER or BEFORE each ROW is acted upon. IIRC after triggers are run after the statement mods are completed so I think right now it's before triggers row1 row1 before triggers row2 row2 after triggers row1 after triggers row2
Dennis Gearon <gearond@cvc.net> writes:
> After reading the manual, this point didn't seem to have been made. Trigger
> fucntions are supposed to return OPAQUE, (i.e. 'void' in 'C/++'
> syntax).
No, OPAQUE doesn't mean "void". In this context it means "a type that
is not known in the SQL type system".
In 7.3 we have largely replaced the use of OPAQUE. It turned out to
need eight new pseudo-types to cover all the shades of meaning that
OPAQUE had acquired over the years :-(.
Trigger functions are now declared to return the pseudo-type TRIGGER.
> My understanding, inferred different parts of diffeent manuals, is the below,
> correct me if I'm wrong:
> BEFORE TRIGGERS
> Can change the values in the NEW Tuple for:
> INSERTS and UPDATES.
Yes.
> Can void the action of:
> INSERTS and UPDATES
> by returning NULL. (does this kill the transaction?)
Yes, and no it doesn't. The particular tuple insert or update is
skipped and the query continues.
> Can stop completely an action with an error message for:
> INSERTS,DELETES, and UPDATES
> by RAISING an EXCEPTION. (This DOES Kill the xaction)
Right.
> AFTER TRIGGERS
> Can stop completely an action with an error message:
> INSERTS,DELETES, and UPDATES
> by RAISING an EXCEPTION. (This DOES Kill the xaction)
Right.
> Have the advantage of seeing the final results of
> an action on a table before canceling it.
Right. In particular, a BEFORE trigger has no way to be sure it's the
last BEFORE trigger.
> ALL TRIGGERS
> Are not DEFERRABLE in anyway. They happen immediately:
> AFTER or BEFORE each ROW is acted upon.
We do have deferrable AFTER triggers, I think. Deferrable BEFORE makes
no sense.
regards, tom lane
Dennis Gearon wrote: > > After reading the manual, this point didn't seem to have been made. Trigger > fucntions are supposed to return OPAQUE, (i.e. 'void' in 'C/++' syntax). But the > manuals also say that BEFORE triggers can return NULL to avoid the INSERT or > UPDATE from occurring. Is this contradictory? Is that actually ONE way to avoid > a UPDATE or INSERTION from happening? > > My understanding, inferred different parts of diffeent manuals, is the below, > correct me if I'm wrong: > > BEFORE TRIGGERS > Can change the values in the NEW Tuple for: > INSERTS and UPDATES. > Can void the action of: > INSERTS and UPDATES > by returning NULL. (does this kill the transaction?) It does not abort the transaction and they can silently suppress DELETE's too. > Can stop completely an action with an error message for: > INSERTS,DELETES, and UPDATES > by RAISING an EXCEPTION. (This DOES Kill the xaction) > > AFTER TRIGGERS > Can stop completely an action with an error message: > INSERTS,DELETES, and UPDATES > by RAISING an EXCEPTION. (This DOES Kill the xaction) > Have the advantage of seeing the final results of > an action on a table before canceling it. They only see the final result up to the row they are fired for. Since the following is right, they would need the ability to predict the future in an INSERT or UPDATE that affects multiple rows. > > ALL TRIGGERS > Are not DEFERRABLE in anyway. They happen immediately: > AFTER or BEFORE each ROW is acted upon. Jan -- #======================================================================# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #================================================== JanWieck@Yahoo.com #