I have the following situation in a table with two date columns, Start_DTand 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 insert a new row, for example, I want to insert the next tuple ('06-02-2012', '10-02-2012'), the End_DTlast (previous) row in chronological order will be updated with the Start_DTrow that I am trying to insert if there is a chronological overlap .
So at the end, after inserting my new row, the table will look like this:
+----+------------+------------+
| 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 generated trigger gives me an error that I understand, giving the way I wrote 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
, , , SELECT FOR UDPATE, . AFTER, , .
:
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;
Oracle, :
Before triggers cannot have INSERT, UPDATE or DELETE statements as their action.
Before triggers cannot call procedures that modify SQL data as their action.
, AFTER, :new :old.
.