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@xxxxxxxxx> 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
