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

Thanks, triggers are new turf for me so nothing is quite obvious. I thought,
the after insert change is snuck in after the rec is created and via some
built in behavior allows mods to the rec without triggering a cascade. But
the reality makes sense and of course I can adjust my work to handle in a
different manner.

I really appreciate you postgres pros taking the time to respond to the
varous newb questions that get posed here.

Tom Lane-2 wrote:
>
> 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
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
> http://archives.postgresql.org/
>
>

--
View this message in context: http://www.nabble.com/Trigger-not-working-as-expected%2C-first-row-gets-a-null-value-tf4238812.html#a12155583
Sent from the PostgreSQL - general mailing list archive at Nabble.com.