Re: Trigger not working as expected, first row gets a null value

novnov <novnovice(at)gmail(dot)com> writes:
> The function is meant to find a value via the internal sql, and then update
> a column in the same tabled that invoked the trigger with that value. Is
> that a non-workable scenario? IE, an after insert trigger can't modify a
> column in the same table that was updated?

Um, if you're trying to do something by modifying the NEW record, that's
correct --- the insertion was already done and you can't change it
retroactively. You could issue a whole new INSERT or UPDATE command,
but beware of creating an infinite loop of trigger firings. It'd be
pretty inefficient anyway since it'd double the work involved for any
change. If you want to modify the row that's going into the table, far
better to do it in a before trigger.

The design rule of thumb is "check or modify the new row in a BEFORE
trigger, propagate the info to other tables in an AFTER trigger".

regards, tom lane