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 <[EMAIL PROTECTED]> 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. ---------------------------(end of broadcast)--------------------------- TIP 6: explain analyze is your friend
