Databases
StackOverflow2013
Postsdbo
POMutating table exception when selecting max(date column of TABLE_X) in an after update trigger for TABLE_X
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POMutating table exception when selecting max(date column of TABLE_X) in an after update trigger for TABLE_X
    primarykey
    data
    text
    <p>I have a trigger somewhat like this (Sorry can't display actual sql because of company rules and this is from a different machine): </p> <pre><code>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; </code></pre> <p>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!</p> <p>EDIT: Also, it seems similar functions such as count,sum,etc... produces the same error. Anyone knows a workaround to this?</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USRam
    1. USRam
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. COuse PRAGMA AUTONOMOUS_TRANSACTION in your declare section and give a try.
      singulars
      1. POMutating table exception when selecting max(date column of TABLE_X) in an after update trigger for TABLE_X
      1. USajmalmhd04
    2. COCan't do an autonomous transaction since I would want the trigger's changes to be rolledback in case of transaction failure.
      singulars
      1. POMutating table exception when selecting max(date column of TABLE_X) in an after update trigger for TABLE_X
      1. USRam
    3. COIndex on `table_x(Filter_condition_1, Filter_Condition_2, SOME_DATE_COL)` ??? Also, filter conditions are constant so can't you hoist the subquery out of the loop into a variable?
      singulars
      1. POMutating table exception when selecting max(date column of TABLE_X) in an after update trigger for TABLE_X
      1. USBen
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload