Re: Trigger UPDATING(field) check false positives in 12c
Date: Wed, 19 Aug 2015 21:56:08 -0500
Message-Id: <0E1F6D69-6DE5-46AA-B2F7-364F83092EB9_at_gmail.com>
Are you sure you don't have a problem with your conditions? That or condition seems to be missing parentheses that may cause it to evaluate as true more than you might expect.
Sent from my iPad
> On Aug 19, 2015, at 8:59 PM, Don Seiler <don_at_seiler.us> wrote:
>
> Evening listers,
>
> Just upgraded the ol' prod database to 12c this week and hit with a real doozy that we didn't see (and can't replicate) in dev or staging. This is 12.1.0.2 with the April PSU on 64-bit Linux.
>
> We have a trigger on a table that does a check to control which fields our app allows to be updated. That list if fields is in another table. I'm going to over simplify it but the logic is loosely like this:
>
> v_current_column := control_pkg.get_current_column();
> for c in (
> select column_name from column_control_table
> where owner='FREEDOM' and table_name='DTS_TEST_TAB'
> order by column_name
> ) loop
> if updating(c.column_name) and (c.column_name != v_current_column or (some other logic here))
> then
> raise exception here
> end if;
> end loop;
>
> The updates on this table are done one column at a time. The logic is basically to verify that we are updating the column through our controlled procedure and not allowing a manual update. It isn't a popular setup and there are plans to move away from it but for now it has claws in a few key parts of the application.
>
> The problem we're seeing is that updating(c.column_name) will return TRUE when we are updating a different column. For example, if our UPDATE statement was:
>
> UPDATE test_tab SET foo='don';
>
> We would see updating('BAR') return true, even though the bar column is not part of the UPDATE statement. This would result in the first two IF checks matching and the exception being thrown and the update disallowed, even though it should have been fine. The real fun is that it only happens on a specific column for this table. In this case the first three columns correctly return false, the 4th one returns true. The column we're actually trying to update is way down the list (48th, sorted alphabetically).
>
> Like I said, we don't see this in the same triggers on dev or staging (which are clones of prod) otherwise obviously we wouldn't have updated prod. And when I try to contrive my own example like one with all_tab_columns, it works as expected as well, no false positives from UPDATING(field) on either dev, staging or prod.
>
> I've got an SR on it but I thought I'd reach out to see if anyone has seen anything like this. I feel like my brain is melting out of my ears after spending all day on this one.
>
> Right now the only workaround is to re-write the trigger to manually check each column to see what's being updated and whether or not to allow it. Each of the 73 columns in this table. And we're seeing it on a similar trigger on another table. You can imagine the fun we'll have if/when we need to add or drop columns. The trigger as it is allowed us to simply loop through the column list procedurally and do the same comparison logic without hard-coding anything.
>
> Thanks.
>
> --
> Don Seiler
> http://www.seiler.us
-- http://www.freelists.org/webpage/oracle-lReceived on Thu Aug 20 2015 - 04:56:08 CEST