before and after triggers

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 #