Quote:
Originally posted by vadlamanibujji
create or replace TRIGGER tr1 AFTER update ON emp FOR EACH ROW
DECLARE
vempno emp.empno%type;
vename emp.ename%type;
vjob emp.vjob%type;
CURSOR C1 IS
SELECT DISTINCT empno,job,ename
FROM emp
where emp.deptno=:new.deptno;
BEGIN
OPEN C1;
LOOP
FETCH C1 INTO vempno,vjob,vename;
EXIT WHEN C1%NOTFOUND;
INSERT INTO dept (lll,mmm)values(vjob,vename)
END LOOP;
CLOSE C1;
END;
the above trigger is giving erro of mutuating how to handle can u plz tellme
|
Wow, what a strange trigger

Anyway, the usual way to avoid mutating table errors involves creating a package and another trigger.
The idea is to move the SELECT to a statement-level trigger, as these don't suffer from mutating table issues. So the idea is brief is:
1) In FOR EACH ROW trigger, just "remember" necessary details of the triggering row (in this case :NEW.deptno is all you need).
2) In AFTER statement trigger, loop through the "remembered" details and do the SELECTs and other processing.
The package is required to do the remembering. In its simplest form the package would be:
CREATE OR REPLACE PACKAGE tr1_pkg IS
-- Declare a PL/SQL table (array) to remember deptno values
TYPE tab_type IS TABLE OF emp.deptno%TYPE;
tab tab_type;
END;
Your row-level trigger becomes:
create or replace TRIGGER tr1 AFTER update ON emp FOR EACH ROW
BEGIN
-- Remember the deptno
tr1_pkg.tab(trg_pkg.tab.COUNT+1) := :NEW.deptno;
END;
The new AFTER statement-level trigger is:
create or replace TRIGGER tr2 AFTER update ON emp
BEGIN
-- Process each remembered row
FOR i IN 1..tr1_pkg.COUNT
LOOP
INSERT INTO dept (lll,mmm)
SELECT job,ename FROM emp WHERE deptno = tr1_pkg.tab(i);
END LOOP;
-- Clear the PL/SQL table for next time
tr1_pkg.tab.DELETE;
END;
(I have taken the liberty of simplifying your code as well)
I hope this helps. I'm afraid there may be some syntax errors in my code because I haven't tried to compile it.