You Asked

I have a trigger somewhat like this (Sorry can't display actual sql because of company rules and this is from a different machine):

create or replace trigger TR_TABLE_X_AU
after update
  on TABLE_X
for each row

declare
  cursor cursor_select_fk is
    select FK_FOR_ANOTHER_TABLE
      from TABLE_Y Y, TABLE_Z Z
     where :NEW.JOINING_COL = Y.JOINING_COL
       and Y.JOINING_COL = Z.JOINING_COL
       and :NEW.FILTER_CONDITION_1 = Y.FILTER_CONDITION_1
       and :NEW.FILTER_CONDITION_2 = Y.FILTER_CONDITION_2
       and :NEW.SOME_DATE_COL = (select max(SOME_DATE_COL)
                                   from TABLE_X
                                  where FILTER_CONDITION_1 = :NEW.FILTER_CONDITION_1 
                                    and FILTER_CONDITION_2 = :NEW.FILTER_CONDITION_2)
begin
  for rec in cursor_select_fk loop
    PCK_SOME_PACKAGE.SOME_PROC(rec.FK_FOR_ANOTHER_TABLE);
  end loop;
end TR_TABLE_X_AU;

We resulted to triggers since it is an enhancement. The nested query selecting the max date seems to be the cause of the problem. Changing it to sysdate results to no exceptions. Any idea on how I can get the max date during the execution of the trigger for TABLE_X? Thanks!

Also, it seems similar functions such as count,sum,etc... produces the same error. Anyone knows a workaround to this?

and Tom said...

A row trigger cannot read from the table it is firing on if it is known that the triggering statement might affect more than one row (and an update can affect more than one row in general).

This error message is in place to save your butt from the fires of "data integrity does not exist in my database anymore". Meaning it is happening to save you from a grievous error in logic.

.. We resulted to triggers since it is an enhancement. ...

i hate that reasoning. Software exists to be changed. Triggers are horrible for trying to automagically slide a change into production. So so so so so many side effects and long term downsides.

http://www.oracle.com/technetwork/issue-archive/2008/08-sep/o58asktom-101055.html

Think about this - you do an update to the SOME_DATE_COL - and it affects more than one row. IF this trigger worked, IF this trigger could read table_x while the update was taking place - think about what would or could happen.

Every time a row is updated, you try to read the "max" value - only remember, we are updating the some_date_col - so you are reading the table in the middle of the update - while the data is being updated (HYPOTHETICALLY, we don't allow such a bad thing to happen). You'll read a max value that was NEVER a max value at any point in time potentially. You'll read and process data that should never have been seen.

For example, suppose the data in the table was:

......   01-JAN-2012 ......
.......  02-JAN-2012 ........

and the update just happened to hit it in that order. the update you fired was:

update table_x set some_date_col = some_date_col + 5 .......;

so that will change 01-jan to 06-jan, AND THEN 02-jan into 07-jan.

The only max dates EVER in this table were 02-jan (before the update) and 07-jan
(after the update). However, what would your trigger hypothetically see??? It would see 01-jan get turned into 06-jan and it would read that out and process it as if that was the max date at some point. BUT IT NEVER WAS. Never.

and then it would process 07-jan as the max date for the second row.

But, it would only do that if the update hit those rows in that specific order. What if the rows were processed:

.......  02-JAN-2012 ........
......   01-JAN-2012 ......

instead? Now - given the same exact set of data, the SAME data, just processed in a different order because of the way the rows existed on disk - we get an entirely different answer!!!! ouch.

so, we do not permit this. We do not allow you to program such inconsistencies into your application.

I strongly (as strongly as I can) encourage you to abandon the trigger and implement the code logic you need as a straightforward code change. Software exists to be modified, that is why we call it software and not hardware.

Your trigger has another potential logic bomb. What if two or more sessions are modifying table_x at the same time? Think about the race conditions that exist now. Might you need to LOCK some data to prevent someone from creating a max(some_date_col) you cannot see? I cannot tell from the code snippet if this is relevant, but it likely could be.

I hesitate to post this next link, you could find it easily enough by searching. It shows how to postpone your processing of the modified data until an AFTER trigger - when the table is 'stable'...

http://asktom.oracle.com/pls/asktom/ASKTOM.download_file?p_file=6551198119097816936

Rating

(2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Made the cause of the problem clear

RJ, May 14, 2013 - 3:22 am UTC

Hi! Thanks for the enlightening response. Yes, the reason "We resulted to triggers since it is an enhancement" is very unprofessional. However, we did result to using a trigger since it recomputes certain variables for certain statistics involving several tables. And I still think that triggers are suitable for the business logic.

After some effort into researching into my problem, I discovered compound triggers. I intended to get the max date at the table statement level part of the compound trigger, and use it during the after each row part of the trigger. However, the query to get the max date contains filtering conditions that should be taken from the record to be updated, making it impossible to get the necessary max date at a table statement level.

I was thinking of creating a separate before each row trigger that will insert the filtering conditions for each row at a temporary table. This trigger should be triggered before the trigger that will compute the max date, which in turn will get the filtering conditions from the temporary table to get the max dates and store it into another temporary table to be used by the after for each part of the compound trigger. Then again I can't arrange the order of triggers since we are just using Oracle 9.2g.

Sorry for the review that turned out to be a question. I am quite new to this site and to Oracle SQL itself. If you would answer, I would be happy to create a new thread for this. Thanks!

May 14, 2013 - 11:37 am UTC

And I still think that triggers are
suitable for the business logic.

I'll respectfully disagree. Point one: your logic approach was entirely botched from day one. Fortunately you were saved by this error. However, there will be other logic errors because of the very nature of triggers - things you won't think of - and that could be avoided by having simple, straight forward, linear procedural code that can be maintained and understood. (this is my biggest gripe with triggers, it makes things so so so so hard to understand)

After some effort into researching into my problem, I discovered compound
triggers. I intended to get the max date at the table statement level part of
the compound trigger, and use it during the after each row part of the trigger.
However, the query to get the max date contains filtering conditions that
should be taken from the record to be updated, making it impossible to get the
necessary max date at a table statement level.

you don't have compound triggers in your really old software. you'll need to use a BEFORE, AFTER FOR EACH ROW and an AFTER trigger (outlined in above referenced article at bottom of the original answer)

save the rowid of the affected row in the row trigger. then iterate over the rowids and you can retrieve the entire modified row. Or save the row values in a collection of records. Quite easily done. And demonstrated in the link provided "Case 2 - you need to access the :old values"

I was thinking of creating a separate before each row trigger that will insert
the filtering conditions for each row at a temporary table.

no, just use a collection - see the link I provided, it shows how to do this.

but seriously, being 100% honest here, change the existing code - don't do this in a trigger as an automagic "really cool" "hey watch this happen" side effect.

I learned a lot

A reader, May 16, 2013 - 10:22 am UTC

Thanks for your guide and for sharing opinion on the matter. I have made the triggers as suggested on the guide you wrote and they now work fine. I used compound triggers as well. It seems that compound triggers are available in my Oracle version. We still went with using triggers though.

Breadcrumb

Question and Answer

You Asked

and Tom said...

Rating

Comments

Made the cause of the problem clear

I learned a lot

More to Explore

PL/SQL demos

PL/SQL docs

Mutating table exception when selecting max(date column of TABLE_X) in an after update trigger for TABLE_X

Breadcrumb

Question and Answer

You Asked

and Tom said...

Rating

Comments

Made the cause of the problem clear

I learned a lot

More to Explore

PL/SQL demos

PL/SQL docs