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

Home -> Community -> Usenet -> c.d.o.misc -> Mutating Error on a row-level trigger.

Mutating Error on a row-level trigger.

From: Steven Markey <s.markey_at_worldnet.att.net>
Date: Sun, 31 Aug 2003 21:07:58 GMT
Message-ID: <Ost4b.121314$0v4.8730853@bgtnsc04-news.ops.worldnet.att.net> 





I have 2 tables:



desc employee;

Name                                                  Null?    Type


----------------------------------------------------- -------- -------------

-----------------------

FNAME                                                 NOT NULL VARCHAR2(15)
MINIT                                                 NOT NULL CHAR(1)
LNAME                                                 NOT NULL VARCHAR2(15)
SSN                                                   NOT NULL CHAR(9)
BDATE                                                          DATE
ADDRESS                                               NOT NULL VARCHAR2(30)
SEX                                                            CHAR(1)
SALARY                                                         NUMBER(5)
SUPERSSN                                                       CHAR(9)
DNO                                                            NUMBER(1)





dept_stat described:



desc dept_stat;

Name                                                  Null?    Type


----------------------------------------------------- -------- -------------

-----------------------

DNAME                                                          VARCHAR2(15)
TOTAL_EMPS                                                     NUMBER
TOTAL_SAL                                                      NUMBER





With a trigger that calculates the total employees and total salary per
department (displaying the dept name) each time an action (specifically an
insert) happens on the employee table and inserts that data in the dept_stat
table.  Here's the code:



CREATE OR REPLACE TRIGGER Update_Dept_State_AIUDR
AFTER INSERT OR UPDATE OR DELETE ON Employee
FOR EACH ROW


/*Trigger captures the current count of employees and the total salary for
each department after a change


  is made to the employees table, and stores this data in the Dept_Stat
table.*/




DECLARE




--Declaring variables to hold data cursor

v_TotalEmps Dept_Stat.Total_Emps%TYPE;


v_TotalSal Dept_Stat.Total_Sal%TYPE;


 v_Dname Dept_Stat.Dname%TYPE;



--Declaring types for holding cursor data and inserts

  TYPE t_TotalEmps IS TABLE of Dept_Stat.Total_Emps%TYPE INDEX BY
BINARY_INTEGER;


  TYPE t_TotalSal IS TABLE of Dept_Stat.Total_Sal%TYPE INDEX BY
BINARY_INTEGER;


  TYPE t_Dname IS TABLE of Dept_Stat.Dname%TYPE INDEX BY BINARY_INTEGER;



--Declaring variables for types


v_type_TotalEmps t_TotalEmps;
v_type_TotalSal t_TotalSal;
v_type_Dname t_Dname;





--Declaring cursor to gather aggregate data

CURSOR c_AggregateData IS


    SELECT dname, COUNT(ssn), SUM(salary)
    FROM employee, department


    WHERE employee.dno = department.dnumber
    GROUP BY dname;



--Declaring variables for indices


v_CursorIndex BINARY_INTEGER;
v_InsertIndex BINARY_INTEGER;
v_LoopIndex BINARY_INTEGER;





BEGIN



         --Initializing cursor index
       v_CursorIndex := 1;

       --open the cursor
       OPEN c_AggregateData;

       --Initial Loop to populate variables
       LOOP
               FETCH c_AggregateData INTO v_Dname, v_TotalEmps, v_TotalSal;

               EXIT WHEN c_AggregateData%NOTFOUND;

               --Initializing elements...walking through elements via loop
above
               v_type_Dname(v_CursorIndex) := v_Dname;
               v_type_TotalEmps(v_CursorIndex) := v_TotalEmps;
               v_type_TotalSal(v_CursorIndex) := v_TotalSal;

               v_CursorIndex := v_CursorIndex + 1;

       END LOOP;

      --Grabbing upper limit for loop
        v_LoopIndex := v_type_Dname.COUNT;

      --Initializing index for element position for looping
       v_InsertIndex := 1;

        --Looping through inserts 1 record at a time by using the table
types and indexes
       FOR FREE_VARIABLE IN 1..v_LoopIndex LOOP

    INSERT INTO Dept_Stat
                  VALUES (v_type_Dname(v_InsertIndex),


v_type_TotalEmps(v_InsertIndex), v_type_TotalSal(v_InsertIndex));



    v_InsertIndex := v_InsertIndex + 1;



       END LOOP;


END Update_Dept_StateAIUDR;



I receive the following error when inserting records into the table via a
script (that uses a WORKING procedure).  The script inserts approx. 10
records.  The error:



"table SCM24.EMPLOYEE is mutating, trigger/function may not see ..."



So, after reading some posts I created a package and 2 triggers (below) that
will do the following:



    
  
  1.   Package, hold a table full of data values tp access throughout the
triggers
  
  2.   Trigger, before an insert is done on Employee I am querying the aggregate
values of the total employees per dept & total salary per dept and storing
them in package tables.
  
  3.   Trigger, after insert on Employee find right dept and add to existing
table these values.  Then loop through to populate dept_stat table.






1st, I created this package to hold the variables for the 2 triggers I use:



CREATE OR REPLACE PACKAGE dept_stat_pack AS




  --Declaring types for holding cursor data and inserts
  TYPE t_TotalEmps IS TABLE of Dept_Stat.Total_Emps%TYPE INDEX BY
BINARY_INTEGER;


  TYPE t_TotalSal IS TABLE of Dept_Stat.Total_Sal%TYPE INDEX BY
BINARY_INTEGER;


  TYPE t_Dname IS TABLE of Dept_Stat.Dname%TYPE INDEX BY BINARY_INTEGER;



  --Declaring variables for types


   v_type_TotalEmps t_TotalEmps;


  v_type_TotalSal t_TotalSal;


  v_type_Dname t_Dname;



  --Declaring variable for index


  v_CursorIndex BINARY_INTEGER;




END dept_stat_pack;



2nd, I created this trigger to query and aggregate the employee table BEFORE
the action is taken on the table:



CREATE OR REPLACE TRIGGER employee_insert_BUIDR
BEFORE INSERT OR UPDATE OR DELETE ON Employee
FOR EACH ROW


DECLARE




--Declaring variables to hold data cursor

v_TotalEmps Dept_Stat.Total_Emps%TYPE;


v_TotalSal Dept_Stat.Total_Sal%TYPE;


 v_Dname Dept_Stat.Dname%TYPE;



--Declaring cursor to gather aggregate data

CURSOR c_AggregateData IS


     SELECT dname, COUNT(ssn), SUM(salary)
    FROM employee, department


    WHERE employee.dno = department.dnumber
     GROUP BY dname;



BEGIN



         --Initializing cursor index
       dept_stat_pack.v_CursorIndex := 1;

       --open the cursor
       OPEN c_AggregateData;

       --Initial Loop to populate variables
       LOOP
               FETCH c_AggregateData INTO v_Dname, v_TotalEmps, v_TotalSal;

               EXIT WHEN c_AggregateData%NOTFOUND;

               --Initializing elements...walking through elements via loop
above
               dept_stat_pack.v_type_Dname(dept_stat_pack.v_CursorIndex) :=
v_Dname;
               dept_stat_pack.v_type_TotalEmps(dept_stat_pack.v_CursorIndex)
:= v_TotalEmps;
               dept_stat_pack.v_type_TotalSal(dept_stat_pack.v_CursorIndex)


:= v_TotalSal;



               dept_stat_pack.v_CursorIndex := dept_stat_pack.v_CursorIndex
+ 1;



       END LOOP;


       CLOSE c_AggregateData;



END employee_insert_BUIDR;



Last but not least after the insert I take the populated table and then grab
the newly inserted values and try to mesh them together for insertion in my
dept_stat table:



CREATE OR REPLACE TRIGGER Update_Dept_State_AIUDR
AFTER INSERT OR UPDATE OR DELETE ON Employee
FOR EACH ROW


DECLARE




--Variable to hold dname of inserted row in employee

v_hold_dname dept_stat.dname%TYPE;



--Declaring variables for indices



v_InsertIndex BINARY_INTEGER;


v_LoopIndex BINARY_INTEGER;



BEGIN


    --Grabbing upper limit for loop


          v_LoopIndex := dept_stat_pack.v_type_Dname.COUNT;


           --Initializing index for element position for looping
            v_InsertIndex := 1;

     --Checking to see if any records are in the table
            IF v_LoopIndex >= 1 THEN

         --Looping through inserts 1 record at a time by using the table
types and indexes
                 FOR FREE_VARIABLE IN 1..v_LoopIndex LOOP

              INSERT INTO dept_stat
                         VALUES (dept_stat_pack.v_type_Dname(v_InsertIndex),


dept_stat_pack.v_type_TotalEmps(v_InsertIndex),
dept_stat_pack.v_type_TotalSal(v_InsertIndex));



              v_InsertIndex := v_InsertIndex + 1;



              END LOOP;



         --Grabbing department name to constrain for updates
         SELECT dname
         INTO v_hold_dname
         FROM dept_stat
         WHERE dname = (select dname from department where dnumber =


:new.dno);


          --Inserting record if dept name is not present
         IF SQL%NOTFOUND THEN

              --Grabbing department name for insertion.
              SELECT dname
              INTO v_hold_dname
              FROM department
              WHERE dname = (select dname from department where dnumber =


:new.dno);


              --Insertion
              INSERT INTO dept_stat VALUES(v_hold_dname, 1, :new.salary);

         --Else update table dept_stat
         ELSE
              --Updating dept_stat w/ new values
              UPDATE dept_stat SET total_emps = total_emps + 1
              WHERE dname = v_hold_dname;

              --Updating dept_stat w/ new values
              UPDATE dept_stat SET total_sal = total_sal + :new.salary
              WHERE dname = v_hold_dname;

          END IF;





    --Inserting record if there are no records in the table
    ELSE



         --Grabbing department name for insertion.
         SELECT dname
         INTO v_hold_dname
         FROM department
         WHERE dname = (select dname from department where dnumber =


:new.dno);


         --Insertion
         INSERT INTO dept_stat VALUES(v_hold_dname, 1, :new.salary);

     END IF;





END Update_Dept_State_AIUDR;




And so all DB objects compile correctly (for this effort), but when I run
the script mentioned above I get:



BEGIN AddEmp('Franklin', 'T', 'Wong', '333445555', '08-Dec-1955', '638 Voss,
Houtston, TX', 'M', 40000, '888665555', 5); END;



*


ERROR at line 1:

ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at "SCM24.UPDATE_DEPT_STATE_AIUDR", line 32
ORA-04088: error during execution of trigger 'SCM24.UPDATE_DEPT_STATE_AIUDR'
ORA-06512: at "SCM24.ADDEMP", line 14
ORA-06512: at line 1






BEGIN AddEmp('Alicia', 'J', 'Zelaya', '999887777', '19-Jul-1968', '3321
Castle, Spring, TX', 'F', 25000, '987654321', 4); END;



*


ERROR at line 1:

ORA-01403: no data found
ORA-01403: no data found
ORA-06512: at "SCM24.UPDATE_DEPT_STATE_AIUDR", line 32
ORA-04088: error during execution of trigger 'SCM24.UPDATE_DEPT_STATE_AIUDR'
ORA-06512: at "SCM24.ADDEMP", line 14
ORA-06512: at line 1

and on..........................................





With all that said, the operation did insert the following rows in the
following tables:



[CRC] SQL> select * from employee;


FNAME           M LNAME           SSN       BDATE     ADDRESS
S     SALARY


--------------- - --------------- --------- --------- ----------------------

-------- - ----------

SUPERSSN         DNO


--------- ----------

John            B Smith           123456789 09-JAN-65 731 Fondren, Houtston,
TX      M      30000
333445555          5






[CRC] SQL> select * from dept_stat;



DNAME           TOTAL_EMPS  TOTAL_SAL



--------------- ---------- ----------


Research                 1      30000






So, any thoughts on were I am going wrong?  I double-checked the SQL in my
queries and cursor and nothing jumps out at me....  Also, what's the better
approach?
Received on Sun Aug 31 2003 - 16:07:58 CDT












Original text of this message













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