Обсуждение: Trigger not working as expected, first row gets a null value
I have a trigger function that updates a field in the update/insert table which uses a not so simple sql statement to find the value to update with. I am not getting the results I expect. I expect I'm falling into some classic trigger gotcha. The table being updated is for 'bids'; 'bids has a fkey to the parent 'item' table. The first time a 'bid' row is added for a particular 'item', the trigger returns null. Any subsequent 'bids' on an 'item' find that the trigger has been invoked and a correct calc applied for the new rows. If value the trigger should apply is hard coded, all table updates get the hard coded value. So there seems to be some kind of timing issue when the complex sql gets involved. But, why does it work after the first row is added for the 'item'? If I change the trigger to be invoked after insert/update, a '0' value is always inserted...I also don't understand that. -- View this message in context: http://www.nabble.com/Trigger-not-working-as-expected%2C-first-row-gets-a-null-value-tf4238812.html#a12061101 Sent from the PostgreSQL - general mailing list archive at Nabble.com.
On Aug 8, 3:20 pm, novnov...@gmail.com (novnov) wrote: > ... I also don't understand ... DDL + sample data, please...
I know that would be best but it'd be a major undertaking to try to repro this situation. I was hoping for some hints, 'it sounds like xyz', because I'm pretty sure I'm just tripping over a commonplace issue. Rodrigo De León-2 wrote: > > On Aug 8, 3:20 pm, novnov...@gmail.com (novnov) wrote: >> ... I also don't understand ... > > DDL + sample data, please... > > > ---------------------------(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#a12062974 Sent from the PostgreSQL - general mailing list archive at Nabble.com.
On Wed, Aug 08, 2007 at 03:20:00PM -0700, novnov wrote: > > I know that would be best but it'd be a major undertaking to try to repro > this situation. I was hoping for some hints, 'it sounds like xyz', because > I'm pretty sure I'm just tripping over a commonplace issue. It doesn't have to be repoducable, but the definition of the tables involves + the code of the trigger would help. I read your description three times and still couldn't quite work out exactly what the problem was or what you were expecting to happen... Have a nice day, -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to litigate.
Вложения
I have been able to spend some more time working on this and have made a bit of progress. I had the trigger set to run before insert; and the sql inside the trigger needed to ref the new record in order to supply a value. I think that's why the first try always applied a null value. So I've changed the trigger to go after insert. Now, all of my trigger function variables are populated properly but the record is never updated. 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? Martijn van Oosterhout wrote: > > On Wed, Aug 08, 2007 at 03:20:00PM -0700, novnov wrote: >> >> I know that would be best but it'd be a major undertaking to try to repro >> this situation. I was hoping for some hints, 'it sounds like xyz', >> because >> I'm pretty sure I'm just tripping over a commonplace issue. > > It doesn't have to be repoducable, but the definition of the tables > involves + the code of the trigger would help. I read your description > three times and still couldn't quite work out exactly what the problem > was or what you were expecting to happen... > > Have a nice day, > -- > Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ >> From each according to his ability. To each according to his ability to >> litigate. > > > -- View this message in context: http://www.nabble.com/Trigger-not-working-as-expected%2C-first-row-gets-a-null-value-tf4238812.html#a12154927 Sent from the PostgreSQL - general mailing list archive at Nabble.com.
novnov <novnovice@gmail.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
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@gmail.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.