SQL> desc tbl_salary; 
 Name                            Null?    Type 
 ------------------------------- -------- ---- 
 RATE                                    NUMBER 
 LOW                                      NUMBER 
 HIGH                                     NUMBER 
 START_DATE                               DATE 
 END_DATE                                 DATE 
 
SQL> select * from tbl_salary; 
RATE    LOW     HIGH    START_DATE       END_DATE 
------- ------- ------- ----------       --------- 
1       2000    3000    1-APR-01         3-AUG-02 
1       2200    3200    3-AUG-02                   <---- Null 
2       2500    4000    23-JUL-01        12-DEC-02 
2       2600    4200    12-DEC-02        11-JAN-03 
2       2800    4400    11-JAN-03                  <---- Null 

1. Check that the new record does not have a start date that is already covered.



2. Update the record for the current RATE to make the END_DATE equal to the START_DATE of the new record (the new record's END_DATE should equals null).

 
create or replace trigger tr_insa_tbl_salary 
after insert on tbl_salary 
for each  row 
declare 
        hold_found      varchar2(1); 
begin 
        select 'Y' into hold_found 
        from tbl_salary 
        where RATE = :new.RATE 
        and end_date is null 
        and start_date > :new.start_date; 
 
exception 
        when NO_DATA_FOUND then 
                raise_application_error(-20000,'Overlapping Dates'); 
end; 
/ 

 
SQL> insert into tbl_salary values (2, 9000, 100000, '25-DEC-02', null); 
insert into tbl_salary values (2, 9000, 100000, '25-dec-93', null) 
            * 
ERROR at line 1: 
ORA-04091: table ALEX.TBL_SALARY is mutating, trigger/function may not see it 
ORA-06512: at line 4 
ORA-04088: error during execution of trigger 'ALEX.TR_INS_TBL_SALARY' 

1. Create the tbl_salary_pkg package that contains PL/SQL tables for holding the TBL_SALARY data. We create three PL/SQL tables - one for holding START_DATE, one for END_DATE, and one for holding the CHANGED_RATE flag that identifies the updated row.



2. Create a BEFORE INSERT STATEMENT trigger that populates the PL/SQL table with the START_DATE, END_DATE and CHANGED_RATE flag ('N').



3. Create the AFTER INSERT ROW trigger that compares a new row with data from tbl_start_date and tbl_end_date tables, assign a new END_DATE value to the tbl_end_date table and update the tbl_changed_rate table.



4. Create the AFTER INSERT STATEMENT trigger to update the tbl_salary table.

 
drop table tbl_salary; 
 
CREATE TABLE TBL_SALARY 
      (RATE NUMBER, 
        LOW NUMBER, 
        HIGH NUMBER, 
        START_DATE DATE, 
        END_DATE DATE); 
 
INSERT INTO TBL_SALARY VALUES (1,2000,3000, '1-apr-01', '3-aug-02'); 
INSERT INTO TBL_SALARY VALUES (1,2200,3200, '3-aug-02', null); 
INSERT INTO TBL_SALARY VALUES (2,2500,4000, '23-Jul-01', '12-dec-02'); 
INSERT INTO TBL_SALARY VALUES (2,2600,4200, '12-dec-02', '11-jan-03'); 
INSERT INTO TBL_SALARY VALUES (2,2800,4400, '11-jan-03', null); 
create or replace package tbl_salary_pkg as 
type datetabtype        is table of date index by binary_integer; 
type chartabtype        is table of char(1) index by binary_integer; 
type rowidtabtype       is table of rowid  index by binary_integer; 
start_date_tab          datetabtype; 
end_date_tab            datetabtype; 
rowid_tab               rowidtabtype; 
changed_rate            chartabtype; 
start_date_tab_size     binary_integer; 
end; 
/ 
create or replace trigger tr_insb_tbl_salary 
before insert on tbl_salary 
declare 
 hold_start_date  date; 
 hold_end_date  date; 
 hold_rowid   rowid; 
 hold_RATE   binary_integer; 
 cursor start_date_cur is 
  select rowid, rate, start_date 
  from tbl_salary 
  where end_date is null 
  order by rate; 
begin 
 open start_date_cur; 
 loop 
  fetch start_date_cur into 
   hold_rowid, hold_rate, hold_start_date; 
  exit when start_date_cur%notfound; 
  tbl_salary_pkg.start_date_tab(hold_rate) := hold_start_date; 
  tbl_salary_pkg.end_date_tab(hold_rate) := hold_end_date; 
  tbl_salary_pkg.rowid_tab(hold_rate) := hold_rowid; 
  tbl_salary_pkg.changed_rate(hold_rate) := 'N'; 
 end loop; 
 tbl_salary_pkg.start_date_tab_size := hold_rate; 
 close start_date_cur; 
end; 
/ 
create or replace trigger tr_insar_tbl_salary 
after insert on tbl_salary 
for each row 
begin 
 if (:new.rate <= tbl_salary_pkg.start_date_tab_size) then 
  if tbl_salary_pkg.start_date_tab(:new.rate) 
    > :new.start_date then 
   raise_application_error(-20001,'Overlapping Dates'); 
  end if; 
  tbl_salary_pkg.end_date_tab(:new.rate) := :new.start_date; 
  tbl_salary_pkg.changed_rate(:new.rate) := 'Y'; 
 else 
  tbl_salary_pkg.start_date_tab(:new.rate) := :new.start_date; 
  tbl_salary_pkg.end_date_tab(:new.rate) := :new.end_date; 
  tbl_salary_pkg.changed_rate(:new.rate) := 'N'; 
  tbl_salary_pkg.start_date_tab_size := 
  tbl_salary_pkg.start_date_tab_size + 1; 
 end if; 
end; 
/ 
create or replace trigger tr_insa_tbl_salary 
after insert on tbl_salary 
begin 
        for i in 1..(tbl_salary_pkg.start_date_tab_size) loop 
                if (tbl_salary_pkg.changed_rate(i) = 'Y') then 
                        update tbl_salary 
                        set end_date = tbl_salary_pkg.end_date_tab(i) 
                        where rowid = tbl_salary_pkg.rowid_tab(i); 
                end if; 
        end loop; 
end; 
/