This content has been marked as final. Show 24 replies
In all fairness, his trigger doesn't open the cursor on updates, only on inserts. And on inserts it doesn't fire. So I tend to believe him that he does not get the mutating error.
I am grabbing the old value before I do the insert. But I will try to gather the before and after data and post an example. Thanks for your interest and time.
Ah, yes. Good point. Missed the fact that the cursor was only opened in the IF INSERTING block. That will still fail if someone tries to INSERT the results of a SELECT statement. But since the WHEN clause prevents the trigger from firing when a row is inserted, that probably isn't a big deal. Of course, once the WHEN clause gets adjusted, then we're likely to see the mutating table exceptions.
If it isn't firing on inserts, then please explain why. This is what I am not understanding. Thanks!
is never true when you are inserting a row. When you are inserting a row, all the :old values are, by definition, NULL. And NULL, by definition, is neither equal to nor unequal to any other value (including NULL).
WHEN (old.sgbstdn_resd_code != new.sgbstdn_resd_code)
Assuming you correct that, then you'll get the mutating table exception on inserts unless you want to force every single user of your table to always and forever insert data only via an INSERT ... VALUES statement and never via an INSERT ... SELECT. I've never come across a situation where that was a reasonable restriction.
Thanks, I will get an example together and see if I can figure out how to explain this better.
I understand what you are saying about the null value, but this is a BEFORE and I thought my cursor was pulling the existing rows sgbstdn_resd_code out and putting it in my variable hold_resd_code.
Sorry, I'm confused. Maybe a nice weekend will help.
Thanks again and have a great weekend.
The point is that your trigger will never even actually reach the point where you're trying to pull the old values.
If the WHEN clause of your trigger doesn't evaluate to TRUE, the trigger does not fire at all. It does nothing.
And if you insert, the old values of that record are NULL (since the record doesn't exist so far). Even if you update, and either the old or the new value of the checked column is NULL, the trigger will do nothing.
Thank you everyone for your assistance. I am going to have to re-work this. I understand why I am not getting data on the insert.
Any ideas on how to solve this would be greatly appreciated.
Well for starters:
WHEN (NVL(old.sgbstdn_resd_code,0) != NVL(new.sgbstdn_resd_code,0))
That depends on whether 0 is a "valid" value for sgbstdn_resd_code. If it's valid, OP will be back with the same question (why doesn't it fire sometimes) ;)