SQL & PL/SQL

Hi all

I have a requirement to update same table using trigger. I am getting mutating trigger error. I totally understand its poor application design but need a solution or a work around

I have a table like this

create table nvtest

(

ref number,

addr varchar2(400),

mobile number

)

with records as  below:

I have created a trigger on that table which when we update one row it has to update another row(sorry the requirement). I could totally understand its the recursive call which cause the oracle mutating table error. Kindly help me out with solutions. The package which makes the first update will call only one row, i have to update the other address type too.

CREATE OR REPLACE TRIGGER nvtesttrigger

   BEFORE INSERT OR UPDATE OR DELETE

   ON nvtest

   REFERENCING NEW AS NEW OLD AS OLD

   FOR EACH ROW

DECLARE

   CURSOR lc_get

   IS

      SELECT * FROM nvtest;

BEGIN

   IF UPDATING

   THEN

      IF :old.addrtyp = 'CORR'

      THEN

         UPDATE nvtest

            SET mobile = :new.mobile

          WHERE addrtyp = 'RESID' AND cliref = :old.cliref;

      ELSIF :old.addrtyp = 'RESID'

      THEN

         UPDATE nvtest

            SET mobile = :new.mobile

          WHERE addrtyp = 'CORR' AND cliref = :old.cliref;

      END IF;

   END IF;

EXCEPTION

   WHEN OTHERS

   THEN

      RAISE;

END;

/