Re: After Insert or Update Trigger Issues!

Kyrill Alyoshin <kyrill(at)technolog(dot)ca> writes:
> 1. MY FUNCTIONS

> CREATE OR REPLACE FUNCTION insert_stamp() RETURNS TRIGGER AS
> $audit_insert$
> BEGIN
> NEW.created_ts := 'now';
> NEW.updated_ts := 'now';
> RETURN NEW;
> END;
> $audit_insert$ LANGUAGE plpgsql;

Do you understand the difference between a BEFORE trigger and an AFTER
trigger? An AFTER trigger fires *after* the operation is done.
Therefore it can't affect the data that was stored. It's no surprise
that the above is a no-op when used as an AFTER trigger; it's just
modifying a row in memory that will be thrown away afterwards.

Usually AFTER triggers are used to propagate data to other tables;
in that scenario, what you want is precisely to know what the final
state of the row is, after all the BEFORE triggers got done doing their
things.

regards, tom lane