For reasons that I cannot determine, the insert triggers no longer work. Oh, they compile fine, they LOOK like they should work but they just....don't. I get errors along the lines of, "attempting to insert a null value...", even though that's what the triggers are supposed to handle.
If I have to manually call sequence.nextval and insert it on my own, there's not much point to having the triggers. Besides, it'll improve performance.
I may be asking the obvious, but have you dropped and then recompiled ? On some heavily used/replicated/just plain weird databases, I've found that Oracle will assure you that the triggers/stored procedures/packages have compiled correctly when they haven't. I am sure there is a scientific reason for all this, but my cargo-cult means of dealing with it is embedding a couple of unmissable logging statements (in an autonomous transaction) and if that doesn't register on the log, just drop/recompile. In general, autonomous transactions are your friend in places like this..it can give you the "before" and "after" states without compromising the transaction.
Perhaps sequence.nextval is the better choice for you. I am personally not comfortable with insert triggers myself (performance, mutation and tuning issues), but I haven't failed to get it to work yet.. Had difficulty, yes.. failed: no.
Re:some suggestions..
djberg96 on 2004-05-18T20:57:46
I may be asking the obvious, but have you dropped and then recompiled?Yep, tried that first, but it still didn't seem to work, so I gave up. I just don't have time to futz with it. I think it's been a good work experience, though - avoid insert triggers.
:)
