-- --Mutating trigger ----

1.   It's  always happend  only on Row level trigger;
2.   Its happend due to trigger try to fired each rows or  try to accmulate the all rows.

Below trigger are the allow to delete the records and save int o the  table - Bin .

--  Count (*) is  count the whole records in  emp and  enter the integer value store the bin table   so that means  the trigger accumalating the whole rows.

-- if   we want to delete the any records  from emp then the trigger become unstable and throw the mutating error

 --- create the table called Bin where the count records will be strore.

 create table bin (C1 number);;


create or replace trigger mut_demo_trg
before delete on emp
for each row
declare
a number;
begin
 select count(*) into a from emp;
 insert into  bin  values(a);
 end;
 /
----- here  we ran the SQL - delete satatement on table EMP.
 SQL> delete from emp where employee_id=101;
delete from emp where employee_id=101
            *
ERROR at line 1:
ORA-04091: table HR.EMP is mutating, trigger/function may not se
ORA-06512: at "HR.MUT_DEMO_TRG", line 4
ORA-04088: error during execution of trigger 'HR.MUT_DEMO_TRG'
--- How to fix this error --
-- create a global variable  via package -  which will collect the records and store the records ..
 create or replace  package global_var
 is
 cnt Pls_integer:=0;
 end;
 /
 ---- create a seprate  statement  level trigger which collect the records  only....
 create or replace trigger mut_demo_trg1
before delete on emp
begin
 select count(*) into global_var.cnt from emp;
 DBMS_output.put_line ('this is the first statement  level trigger table' );
 end;
 /
  ----- Create a statement level trigger which allow to store the count  in bin table via  global variable.
  create or replace trigger mut_demo_trg2
before delete on emp
begin
 insert into bin values (global_var.cnt);
 DBMS_output.put_line ('this is the triggering table which allow us to insert the data in table' );
 end;
 /

---- Now drop the old  trigger  which is throwing the muttaing errror :---
 drop trigger mut_demo_trg;
 -- now the oracle allow us to delete the  records from table emp:

SQL> delete from emp where employee_id=101;

1 row deleted.

SQL>

===================================
another example

---Mutating trigger --
-- row trigger
--oracle enforce certain restriction on row trigger  to have a consistent view of data
--during the execution of triggers
-- A mutating table is the table which is affected by DML statement or indirectly because of on_delete_cascade clause in refrel
-- integratetiy constraint

/*
---
sample of mutating trigger
create trigger trg1
before insert on feespaid
for each row
-----
*/

-- create mutating table
/*create table MUTATING_DEMO (a number);
insert into MUTATING_DEMO values (1);
commit;
create or replace trigger trg1
before insert on MUTATING_DEMO
for each row
declare
lV_MAX number;
begin
  select max(a) into lV_MAX from MUTATING_DEMO;
  :new.a:=lV_MAX+1;
\*  dbms_output.put_line('new value' ||' '|| :new);
  dbms_output.put_line('new.a value' ||' '|| :new.a);*\
  dbms_output.put_line('new value' ||' '|| lV_MAX);
  dbms_output.put_line('new value' ||' '|| lV_MAX+1);
  end;
  /*/