1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Working Around Mutating Table error with views

Discussion in 'Other Development Tools' started by kiran.marla, Feb 26, 2010.

  1. kiran.marla

    kiran.marla Forum Genius

    Messages:
    403
    Likes Received:
    52
    Trophy Points:
    505
    Location:
    Khammam
    WORKING AROUND MUTATING TABLE ERROR USING VIEW


    Code (SQL):
    SQL> CREATE TABLE STUD(RNO NUMBER(5), SNAME VARCHAR2(20), M1 NUMBER(4), M2   NUMBER(4),
                   M3 NUMBER(4), TOT NUMBER(7), AVGG NUMBER(5,2));
     
    Table created.

    I created a table stud with the columns, the work is that as I enter rno, sname, m1, m2, m3 tot and avgg are calculated and inserted into same table. Here I created trigger which is doing that.

    Code (SQL):
    SQL> ED
    Wrote file afiedt.buf

      1  CREATE OR REPLACE TRIGGER STUDTRIG
      2  AFTER INSERT OR DELETE OR UPDATE
      3  ON STUD
      4  FOR EACH ROW
      5  BEGIN
      6  IF INSERTING THEN
      7  INSERT INTO STUD(TOT,AVGG) VALUES((:NEW.M1+:NEW.M2+:NEW.M3),ROUND(((:NEW.M1+:NEW.M2+:NEW.M3)/3),2));
      8  END IF;
      9* END;
    SQL> /

    TRIGGER created.
     
    Tringger is created, when I try to insert the values into stud table, the following things are happening.

    Code (SQL):
    SQL> INSERT INTO STUD(RNO,SNAME,M1,M2,M3) VALUES(1,'KIRAN',87,98,87);
    INSERT INTO STUD(RNO,SNAME,M1,M2,M3) VALUES(1,'KIRAN',87,98,87)
                *
    ERROR at line 1:
    ORA-04091: TABLE KIRAN.STUD IS mutating, TRIGGER/FUNCTION may NOT see it
    ORA-06512: at "KIRAN.STUDTRIG", line 3
    ORA-04088: error during execution OF TRIGGER 'KIRAN.STUDTRIG'
     
    To work around that, i.e with out taking 2nd table , I created one view.


    Code (SQL):
    SQL> CREATE VIEW STUDVIEW AS SELECT * FROM STUD;

    VIEW created.
     
    I create a instead of trigger(trigger written on views), the following code shows that.



    Code (SQL):
    SQL> ED
    Wrote file afiedt.buf


    SQL> CREATE OR REPLACE TRIGGER STUDTRIG
      2  INSTEAD OF INSERT OR DELETE OR UPDATE
      3  ON STUDVIEW
      4  FOR EACH ROW
      5  BEGIN
      6  IF INSERTING THEN
      7  INSERT INTO STUD VALUES(:NEW.RNO,:NEW.SNAME,:NEW.M1,:NEW.M2,:NEW.M3,
      8  (:NEW.M1+:NEW.M2+:NEW.M3),ROUND(((:NEW.M1+:NEW.M2+:NEW.M3)/3),2));
      9  END IF;
     10  END;
     11  /

    TRIGGER created.

     
    Now I am inserting the values into views, rno, sname, m1, m2, m3 values are entered.

    Code (SQL):
    SQL> INSERT INTO STUDVIEW(RNO,SNAME,M1,M2,M3) VALUES(1,'KIRAN',87,98,87);

    1 ROW created.

     
    Now checking the values are entered into base table or not

    Code (SQL):
    SQL> SELECT * FROM STUD;

      RNO SNAME                   M1    M2    M3   TOT  AVGG                                            
    ----- -------------------- ----- ----- ----- ----- -----                                            
        1 KIRAN                   87    98    87   272 90.67                                            

    SQL> ED ST

    SQL> @ST;
    TRIGGER created.
    Use nvl command for individual marks , to avoid calculation with null values.

    Code (SQL):
    SQL> GET ST
      1  CREATE OR REPLACE TRIGGER STUDTRIG
      2  INSTEAD OF INSERT OR DELETE OR UPDATE
      3  ON STUDVIEW
      4  FOR EACH ROW
      5  BEGIN
      6  IF INSERTING THEN
      7  INSERT INTO STUD VALUES(:NEW.RNO,:NEW.SNAME,:NEW.M1,:NEW.M2,:NEW.M3,
      8  NVL(:NEW.M1,0)+NVL(:NEW.M2,0)+NVL(:NEW.M3,0)),
                                                                     ROUND(((NVL(:NEW.M1,0)+NVL(:NEW.M2,0)+NVL(:NEW.M3,0))/3),2));
      9  END IF;
     10* END;
     11  /

    TRIGGER created.

    SQL> INSERT INTO STUDVIEW(RNO,SNAME,M1,M2) VALUES(1,'MARLA',67,75);

    1 ROW created.

    SQL> SELECT * FROM STUD;

      RNO SNAME                   M1    M2    M3   TOT  AVGG                                            
    ----- -------------------- ----- ----- ----- ----- -----                                            
        1 KIRAN                   87    98    87   272 90.67                                            
        1 MARLA                   67    75         142 47.33
    Hope this is clear.

    Comments are encouraged

    Thanq
     
  2. Sadik

    Sadik Community Moderator Forum Guru

    Messages:
    1,906
    Likes Received:
    252
    Trophy Points:
    1,455
    Promoted to Article.
     
  3. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    Another Method to avoid the mutation for the same issue. (without using Instead of view trigger.

    Code (SQL):

    SQL> CREATE TABLE STUD(RNO NUMBER(5),
      2               SNAME VARCHAR2(20),
      3               M1 NUMBER(4),
      4               M2   NUMBER(4),
      5               M3 NUMBER(4),
      6               TOT NUMBER(7),
      7               AVGG NUMBER(5,2));

    TABLE created.

    SQL> CREATE OR REPLACE TRIGGER STUDTRIG
      2  BEFORE INSERT
      3  ON STUD
      4  FOR EACH ROW
      5  BEGIN
      6     IF INSERTING THEN
      7
      8     :NEW.TOT := NVL(:NEW.M1,0)+NVL(:NEW.M2,0)+NVL(:NEW.M3,0);
      9     :NEW.AVGG := ROUND(((NVL(:NEW.M1,0)+NVL(:NEW.M2,0)+NVL(:NEW.M3,0))/3),2)
    ;
     10
     11     END IF;
     12
     13  END;
     14  /

    TRIGGER created.

    SQL> SELECT * FROM STUD;

    no ROWS selected

    SQL> INSERT INTO STUD(RNO,SNAME,M1,M2,M3) VALUES(1,'KIRAN',87,98,87);

    1 ROW created.

    SQL> INSERT INTO STUD(RNO,SNAME,M1,M2) VALUES(1,'MARLA',67,75);

    1 ROW created.

    SQL> SELECT * FROM STUD;

           RNO SNAME              M1         M2         M3        TOT       AVGG
    ---------- ---------- ---------- ---------- ---------- ---------- ----------
             1 KIRAN              87         98         87        272      90.67
             1 MARLA              67         75                   142      47.33

    SQL>

     
     
  4. Sadik

    Sadik Community Moderator Forum Guru

    Messages:
    1,906
    Likes Received:
    252
    Trophy Points:
    1,455
    Excellent method Raj
     
  5. kiran.marla

    kiran.marla Forum Genius

    Messages:
    403
    Likes Received:
    52
    Trophy Points:
    505
    Location:
    Khammam
    Excellent work Raj, i wonder how the rows are inserted without writing insert statement explicitly. Can u please explain.
    Kiran Marla
     
  6. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    Here in the trigger (BEFORE INSERT) ,

    It assigns the Tot and average valuesBEFORE INSERT . Then ON INSERT , it inserts those values alosng with Number and name.

    ie. It will insert correctly , even you enter the tot and average wrongly.

    Code (SQL):

    SQL> SELECT * FROM stud;

           RNO SNAME              M1         M2         M3        TOT       AVGG
    ---------- ---------- ---------- ---------- ---------- ---------- ----------
             1 KIRAN              87         98         87        272      90.67
             1 MARLA              67         75                   142      47.33

    SQL> INSERT INTO stud VALUES (10,'RAJ',50,60,70,100,50);

    1 ROW created.

    SQL> SELECT * FROM stud;

           RNO SNAME              M1         M2         M3        TOT       AVGG
    ---------- ---------- ---------- ---------- ---------- ---------- ----------
             1 KIRAN              87         98         87        272      90.67
             1 MARLA              67         75                   142      47.33
            10 RAJ                50         60         70        180         60

    SQL>