Got bit by a bug in our application today. The code in question inserts into the site table, calls lastval(), then takes that value and inserts into host, calls lastval() again, then inserts into application table. (We’re building relationship trees). The problem? We added a new trigger to the site table, to have it do an insert into another table to handle some advanced messaging. Unfortunatly this table had a sequence on it…
What ended up happening was we were adding sites fine for a couple hours, but messaging was broken. By the time we tracked that down, we got messaging working (almost) but then site insertion started tossing errors. Fooy! We chase a number of red herrings but finally realized that our lastval() calls were generating imroper sequence numbers! So we are now replacing all calls to lastval() with a curval() and the appropriate sequence name.
Anyone else been bitten by that? I actually told my app developers we could run into this problem in the future when we first decided to use lastval(), but we wanted to use some new 8.1 goodness and also thought this was a good way to hide the sequence machinations from our timid junior app developer, plus I didn’t figure it would happen so quick… we never even made it through one release cycle! So, I am hereby declaring lastval() as evil, or at least a problem waiting to happen… beware the lastval()!!
:-)