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?
--
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/
Show quoted text
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?
--
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.