1

I have the following situation in a table with two date columns, Start_DT and End_DT.

+----+------------+------------+
| ID |  Start_DT  |   End_DT   |
+----+------------+------------+
|  1 | 01-01-2012 | 02-01-2012 |
|  2 | 02-01-2012 | 05-02-2012 |
|  3 | 05-02-2012 | 07-02-2012 |
+----+------------+------------+

Whenever I'm inserting a new row, for example I want to insert the following tuple ('06-02-2012', '10-02-2012'), the End_DT of the last (previous) row in chronological order, will be updated with the Start_DT of the row I'm trying to insert, if there is a chronological overlap.

So, in the end, after inserting my new row, the table will look like:

+----+------------+------------+
| ID |  Start_DT  |   End_DT   |
+----+------------+------------+
|  1 | 01-01-2012 | 02-01-2012 |
|  2 | 02-01-2012 | 05-02-2012 |
|  3 | 05-02-2012 | 06-02-2012 |
|  4 | 06-02-2012 | 10-02-2012 |
+----+------------+------------+

The problem is that the trigger I've created is giving me an error, which I understand giving the way I've written my trigger, but I don't know how to modify it in order to achieve what I want:

SQL Error: ORA-04091: table HISTORIC_TIME_TABLE is mutating, trigger/function may not see it

We considered that it is mandatory to use SELECT FOR UDPATE in order to put a lock on the previous row, in order to make the update. But changing this trigger into a AFTER, I'm not sure if we can achieve the same lock on the table.

My trigger's code is:

create or replace trigger trg_update_historic_time_table
before insert on HISTORIC_TIME_TABLE
for each row
declare

cursor upd_hist_tt_cur
is
    select start_dt, end_dt
    from HISTORIC_TIME_TABLE
    where (end_dt > sysdate)
    for update of end_dt;

begin

    for hist_cur_r in upd_hist_tt_cur loop
        if hist_cur_r.start_dt < :new.start_dt then
            update HISTORIC_TIME_TABLE
                set  end_dt = :new.start_dt
            where (start_dt = hist_cur_r.start_dt);
            commit;
        else
            :new.end_dt :=  hist_cur_r.start_dt;
        end if;
    end loop;

exception when no_data_found then null;
end;

I have found online on the Oracle docs page that BEFORE triggers cannot modify data in the same table:

Before triggers cannot have INSERT, UPDATE or DELETE statements as their action.
Before triggers cannot call procedures that modify SQL data as their action.

So, I'm thinking about modifying my trigger to a AFTER type, but then I wouldn't have access to my :new and :old variables.

Any help is greatly appreciated.

Radu Gheorghiu
  • 20,049
  • 16
  • 72
  • 107
  • Do you need a solution for `mysql` too? As you tagged it. – Lalit Kumar B Oct 16 '15 at 11:19
  • @LalitKumarB No, StackOverflow suggested MySQL too and I thought it might reach more people, who might know. I removed the tag. – Radu Gheorghiu Oct 16 '15 at 11:21
  • 1
    This [solution](http://stackoverflow.com/questions/5805986/oracle-after-delete-trigger-how-to-avoid-mutating-table-ora-04091) does not help you? It is the almost standard way to read the table you are modifying. (get the data into a collection on stmt level, then read through collection on row level) – Florin Ghita Oct 16 '15 at 11:25
  • Or this question's answers: http://stackoverflow.com/questions/2138363/oracle-triggers-problem-with-mutating-tables?rq=1 – Florin Ghita Oct 16 '15 at 11:29
  • @FlorinGhita I'll take a look. Thank you – Radu Gheorghiu Oct 16 '15 at 11:31
  • Why did you want to do a `select for update` rather than simply updating the previous row as part of the after statement trigger that would be part of the three-trigger solution in the duplicate question? – Justin Cave Oct 16 '15 at 13:07
  • @JustinCave Well, I would prefer your solution too, but the "problem" this is a requirement from an architectural point of view (not my decision). This is a little over my head on how I should create my collection and then use it with your triggers. – Radu Gheorghiu Oct 16 '15 at 13:09
  • I'm not following? What is an "architectural requirement"? That you do a `select for update` before updating the row? As part of a trigger? That requirement makes no sense. It would make sense for the application to lock the overlapping rows before doing the new `insert`. But then you wouldn't have any logic in a trigger, you'd issue the `update` from the application that acquired the lock. – Justin Cave Oct 16 '15 at 13:12
  • @JustinCave It has been a decision of the application architect. He said it is mandatory to use `select for update` in order to maintain a isolation level during the trigger, so the previous row is not being updated (possibly by another similar query), while we insert the new row. – Radu Gheorghiu Oct 16 '15 at 14:03
  • @RaduGheorghiu Even if you succeeded with this ideea(lock on previous rows) you still can get some mess in the table(if it updates two rows). What do you want is a complete serialization of inserts. This can be achieved simply by using an `alter table x lock` before the insert, then do the update, the insert, and then commit. – Florin Ghita Oct 16 '15 at 14:31
  • 1
    @RaduGheorghiu - The decision, at least as you've described it, makes no sense. Once you're in the trigger, you've already started inserting the new row. Within the trigger (or set of triggers in this case), you can't know what row(s) are being inserted until after the rows have been inserted. Doing a `select for update` in the after statement just before updating that same row in the after statement trigger is beyond pointless. If you want to do a `select for update`, you want to abandon the trigger idea and have the application issue the insert of the new row and the update of the old one. – Justin Cave Oct 16 '15 at 14:47
  • @JustinCave Agreed and it is what I have recommended. Running the entire code(select for update+insert+update) in an anonymous block would be suficient, right? (with corresponding exception handling) – Radu Gheorghiu Oct 16 '15 at 14:53
  • @RaduGheorghiu - Define "sufficient". You can still end up with inconsistent data. Unless you are planning to lock every row in the table every time (which would be rather expensive), you could still potentially have data consistency issues if, for example, two different sessions are simultaneously inserting rows that don't overlap with existing rows but do overlap with each other. Assuming that sort of thing is unlikely, it sounds like a reasonable approach. – Justin Cave Oct 16 '15 at 14:58
  • @JustinCave That should never happen since when inserting two rows,uu whatever row gets inserted first, the second should be inserted (and update its End_DT) relative to the first (last) row. – Radu Gheorghiu Oct 16 '15 at 15:03

0 Answers0