HOW TO REMOVE MUTATING TABLE ERROR WITH THE HELP  OF AUTONOMOUS            TRANSACTION   DOWNLOAD EXAMPLE


 1) CREATE  A TABLE STUDENT WITH ROLLNO PRIMARY KEY AND NAME COLUMN

     DROP TABLE STUDENT CASCADE CONSTRAINTS;

   CREATE TABLE STUDENT(ROLLNO NUMBER PRIMARY KEY,NAME VARCHAR2(20));


 2) INSERT SOME ROWS IN THE TABLE

    INSERT INTO STUDENT VALUES(1,'PUSHPJEET');

    INSERT INTO STUDENT VALUES(2,'PRIYANKA');

    INSERT INTO STUDENT VALUES(3,'ROHIT');



 3) COMMIT THE ROWS  INSERTED

     COMMIT;

 4)CREATE TRIGGER  ON  STUDENT WHICH WE HAVE CREATED


 CREATE  OR REPLACE TRIGGER MUTATING_TRIGGER  AFTER DELETE ON STUDENT

  FOR EACH ROW
  DECLARE
  VNAME VARCHAR2(20);
  BEGIN
  SELECT NAME INTO VNAME
  FROM STUDENT WHERE
  ROLLNO=1;
  DBMS_OUTPUT.PUT_LINE('STUDENT NAME FOR ROLLNO 1 IS '||VNAME);
  END MUTATING_TRIGGER;

 /

 5) NOW WE  WILL TRY TO DELETE ROWS FROM STUDENT  AND THIS WILL FIRE            THE      TRIGGER WE CREATED
 DELETE FROM STUDENT;


THIS TRIGGER WILL GIVE YOU MUTATING TABLE ERROR BECAUSE THE TIGGER  WILL TRY TO INSERT IN THE SAME  TABLE FROM WHICH WE ARE DELETING ROWS. IT LEADS TO MUTATING  TABLE  ERROR

 ORA-04091: table SQL.STUDENT is mutating, trigger/function may not see it
 ORA-06512: at "SQL.MUTATING_TRIGGER", line 3
 ORA-04088: error during execution of trigger 'SQL.MUTATING_TRIGGER'


NOW MAKE THE TRIGGER AUTONOMOUS TO AVOID THE MUTATING TABLE ERROR.DECLARE TRANSACTION TYPE AND COMMIT THE TRANSACTION.

 6) BEFORE CREATING THE NEW TRIGGER LETS DROP THE OLD TRIGGER

     DROP  TRIGGER MUTATING_TRIGGER;

 7) NOW WE WILL ADD PRAGMA AUTONOMOUS_TRANSACTION CLAUSE  IN OUR          TRIGGER. YOU CAN ALSO WRITE COMMIT  IN THIS TYPE OF TRIGGER.
  CREATE OR REPLACE TRIGGER MUTATING_TRIGGER

  AFTER DELETE ON STUDENT

  FOR EACH ROW

  DECLARE

    PRAGMA AUTONOMOUS_TRANSACTION;
    VNAME VARCHAR2(20);

  BEGIN

   SELECT NAME INTO VNAME
   FROM STUDENT WHERE   ROLLNO=1;

  DBMS_OUTPUT.PUT_LINE('STUDENT NAME FOR ROLLNO 1 IS '||VNAME);

  COMMIT;

 END MUTATING_TRIGGER;

 /

 TRIGGER CREATED WITH PRAGMA AUTONOMOUS_TRANSACTION CLAUSE AND WE WROTE COMMIT ALSO IN THE TRIGGER

 8) NOW WE  WILL TRY TO DELETE ROWS FROM STUDENT  AND THIS WILL FIRE THE TRIGGER WE CREATED

 DELETE FROM STUDENT;

NOW WE ARE ABLE TO DELETE THE ROWS AND TRIGGER IS ABLE TO HANDLE THE MUTATING TRIGGER ERROR.TRIGGER IS NOW ABLE TO HANDLE MUTATING TABLE ERROR AND YOU CAN OBSERVE  THAT IT WAS ROW LEVEL TRIGGER SO 3 ROWS DELETED AND TRIGGER ALSO FIRES 3 TIMES RESULTING IN OUTPUT FROM TRIGGER 3 TIMES.

 CONGRATULATION YOU SUCCESSFULLY  COMPLETED THIS EXAMPLE