************************************************** ************************************
SQL> insert into emp_test (empno, empname, join_date, empsal, emp_desig) values (11, 'Alan',
'01-apr-04', 12000, 'PROGRAMMER');
insert into emp_test (empno, empname, join_date, empsal, emp_desig) values (11, 'Alan', '01-a
*
ERROR at line 1:
ORA-04091: table PRACTICE.EMP_TEST is mutating, trigger/function may not see it
ORA-06512: at "PRACTICE.TEST_TRIG", line 9
ORA-06512: at "PRACTICE.TEST_TRIG", line 13
ORA-04088: error during execution of trigger 'PRACTICE.TEST_TRIG'
************************************************** *****************************
Body of the Trigger is as follows:
################################################## ######SET SERVEROUTPUT ON
SET VERIFY OFF
create or replace trigger test_trig
after insert on emp_test for each row
declare
getempno number(5);
getempname varchar2(50);
getjoindate date;
getsal number(10,2);
getdesig varchar2(50);
cursor empcur is
select empno, empname, join_date, empsal, emp_desig from
emp_test where empno=(select max(empno) from emp_test);
cur_exp exception;
begin
open empcur;
loop
fetch empcur into getempno, getempname, getjoindate, getsal,
getdesig;
exit when empcur%NOTFOUND;
insert into newemp_test values(getempno, getempname,
getjoindate, getsal, getdesig);
end loop;
if empcur%ROWCOUNT = 0 then
close empcur;
raise cur_exp;
end if;
exception
when cur_exp then
DBMS_OUTPUT.PUT_LINE('No Record found. ! ');
end;
/
SET VERIFY ON
SET SERVEROUTPUT OFF
################################################## ###########