Results 1 to 2 of 2

Thread: trighelp

  1. #1
    Join Date
    Oct 2002
    Posts
    36

    Unanswered: trighelp

    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

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: trighelp

    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •