Re: before and after triggers

Dennis Gearon <gearond(at)cvc(dot)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