Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Some Questions

Re: Some Questions

From: ykhogaly <ykhogaly_at_us.oracle.com>
Date: Wed, 23 Feb 2000 08:46:54 -0700
Message-ID: <890veg$qua$1@inet16.us.oracle.com>


Hi,

I'll answer the Mutating Table question on this email !

The mutating table problem comes when the trigger tries to select or modify any row of the same table. This situation comes when a table preserves some effective date.

To describe this method, I will be using the table SALGRADE. The table SALGRADE contains information on salary limits for each grade. The salary limits are also based on a time factor, i.e. the employees' salary is determined by checking which grade level was effective when they joined or reviewed, not necessarily the grade effective now.

So the table looks like this:

SQL> desc salgrade;

 Name                            Null?    Type
 ------------------------------- -------- ----
 GRADE                                    NUMBER
 LOSAL                                    NUMBER
 HISAL                                    NUMBER
 START_DATE                               DATE
 END_DATE                                 DATE

The table data looks like:

GRADE   LOSAL   HISAL   START_DATE       END_DATE
------- ------- ------- ----------       ---------
1       1000    2000    1-APR-94         3-AUG-95
1       1200    2200    3-AUG-95                   <---- Null
2       1500    3000    23-JUL-92        12-DEC-93
2       1600    3200    12-dec-93        11-jan-95
2       1800    3400    11-JAN-95                  <---- Null

This means the effective salary range of Grade 1 now is (1200-2200) nut the employees who had review between 1-APR-94 to 3-AUG-95 will be in the range (1000-2000). This is a purely hypothetical scenario. Our objective is to devise a trigger that does the following when a new record is inserted:

(1) Integrity checking for overlapping dates, i.e. the new record can't have a start date that is already covered.

(2) Update the record for the current grade to make the end_date equal to the start date of the new record (the new record's end_date must be null as

    that is the current record).

In both cases the table SALGRADE has to be selected and updated on the after insert row trigger on the same table. But the table will be mutating when the
trigger fires and thus a run-time error will occur.

For the first requirement, consider the following trigger:

create or replace trigger taiudr_salgrade after insert on salgrade
for each row
declare

        hold_found varchar2(1);
begin

        select 'Y' into hold_found
        from salgrade
        where grade = :new.grade
        and end_date is null
        and start_date > :new.start_date;

exception
        when NO_DATA_FOUND then
                raise_application_error(-20000,'Overlapping Dates');
end;
/

Although the trigger can be created with no errors, when a user tries to insert into the table he will receive the mutating table error:

SQL> insert into salgrade values (2, 9000, 100000, '25-dec-95', null); insert into salgrade values (2, 9000, 100000, '25-dec-93', null)

            *
ERROR at line 1:

ORA-04091: table JACK.SALGRADE is mutating, trigger/function may not see it
ORA-06512: at line 4
ORA-04088: error during execution of trigger 'JACK.TAIUDR_SALGRADE'

SOLUTION


The following approach is another possibility for the task:

  1. Create a package "SALGRADE_PKG" that contains PL/SQL tables for holding the SALGRADE data. Here we create 3 tables - one for holding start_dates, one for end_dates, and one for holding the change_flag that identifies the updated row.
  2. Create a BEFORE INSERT STATEMENT trigger that populates the PL/SQL table with the start dates, end_dates and changed_grades flag ('N').
  3. Create an AFTER INSERT ROW trigger that compares the newly inserted row against this PL/SQL table not the Database table. This way the integrity check can be done. The same trigger should assign the new end_date value to the PL/SQL table and update the value of the flag to indicate that this has to be changed.
  4. Create a AFTER INSERT STATEMENT trigger to update the SALGRADE table with the values in the PL/SQL table after looking at the change flag.

All these programs can be created by the sources found below. I urge you to test them and make any enhancements to them as you find necessary.

CODE:


Code to create test table and populate it with data:

drop table salgrade;

CREATE TABLE SALGRADE

      (GRADE NUMBER,
        LOSAL NUMBER,
        HISAL NUMBER,
        START_DATE DATE,
        END_DATE DATE);

INSERT INTO SALGRADE VALUES (1,1000,2000, '1-apr-94', '3-aug-95');
INSERT INTO SALGRADE VALUES (1,1200,2200, '3-aug-95', null);
INSERT INTO SALGRADE VALUES (2,1500,3000, '23-Jul-92', '12-dec-93');
INSERT INTO SALGRADE VALUES (2,1600,3200, '12-dec-93', '11-jan-95'); INSERT INTO SALGRADE VALUES (2,1800,3400, '11-jan-95', null);

Code for package STEP 1 above:

create or replace package salgrade_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_grade           chartabtype;
start_date_tab_size     binary_integer;
end;
/

Code for before insert statement trigger STEP 2 above:

create or replace trigger tbiuds_salgrade before insert on salgrade
declare

 hold_start_date  date;
 hold_end_date  date;
 hold_rowid   rowid;
 hold_grade   binary_integer;

 cursor start_date_cur is
  select rowid, grade, start_date
  from salgrade
  where end_date is null
  order by grade;
begin
 open start_date_cur;
 loop
  fetch start_date_cur into
   hold_rowid, hold_grade, hold_start_date;   exit when start_date_cur%notfound;
  salgrade_pkg.start_date_tab(hold_grade) := hold_start_date;
  salgrade_pkg.end_date_tab(hold_grade) := hold_end_date;
  salgrade_pkg.rowid_tab(hold_grade) := hold_rowid;
  salgrade_pkg.changed_grade(hold_grade) := 'N';
 end loop;
 salgrade_pkg.start_date_tab_size := hold_grade;  close start_date_cur;
end;
/

Code for after insert row trigger STEP 3 above:

create or replace trigger taiudr_salgrade after insert on salgrade
for each row
begin
 if (:new.grade <= salgrade_pkg.start_date_tab_size) then   if salgrade_pkg.start_date_tab(:new.grade)     > :new.start_date then
   raise_application_error(-20001,'Overlapping Dates');   end if;
  salgrade_pkg.end_date_tab(:new.grade) := :new.start_date;   salgrade_pkg.changed_grade(:new.grade) := 'Y';  else

  salgrade_pkg.start_date_tab(:new.grade) := :new.start_date;
  salgrade_pkg.end_date_tab(:new.grade) := :new.end_date;
  salgrade_pkg.changed_grade(:new.grade) := 'N';
  salgrade_pkg.start_date_tab_size :=

    salgrade_pkg.start_date_tab_size + 1;  end if;
end;
/

Code for after insert statement trigger STEP 4 above:

create or replace trigger taiuds_salgrade after insert on salgrade
begin

        for i in 1..(salgrade_pkg.start_date_tab_size) loop
                if (salgrade_pkg.changed_grade(i) = 'Y') then
                        update salgrade
                        set end_date = salgrade_pkg.end_date_tab(i)
                        where rowid = salgrade_pkg.rowid_tab(i);
                end if;
        end loop;

end;
/

Once all the package and triggers are created, the same insert doesn't succeed
but gives the user the a more appropriate error:

SQL> insert into salgrade values (2, 9000, 100000, '25-dec-93', null); insert into salgrade values (2, 9000, 100000, '25-dec-93', null)

            *
ERROR at line 1:

ORA-20001: Overlapping Dates
ORA-06512: at line 5
ORA-04088: error during execution of trigger 'JACK.TAIUDR_SALGRADE'

CONCLUSION:


This method is not submitted as a solution to all cases, however, it is provided as one possibility. The test and code are provided so modifications
can be made to suit the individual cases. Be sure to test this completely before relying on it's functionality.

"The Views expressed here are my own and not necessarily those of Oracle Corporation"

<mihou_at_yahoo.com> wrote in message news:890t0d$tc0$1_at_nnrp1.deja.com...

> HI EVERYONE!
> CAN SOMEONE TELL ME WHAT IS THE WORTH OF:
> 1/ ANALYSE TABLE_NAME ESTIMATE STATISTICS;
>
> 2/ANALYSE TABLE_NAME COMPUTE STATISTICS;
> 3/Can I have a good example in order to understand the proper use of
> MYSEQUENCE.CURRVAL?
> 4/What do I have to do to avoid MUTATING TABLE while using a trigger.?
> (an example would be appreciated)
> 5/Does someone know a site where I can submit questions on
> Forms,Reports and PL/SQL?
>
> Thanks for your help.
>
> Mihou
>
>
>
> Sent via Deja.com http://www.deja.com/

> Before you buy.


Received on Wed Feb 23 2000 - 09:46:54 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US