Recursive trigger solution

Actually, my previous compound trigger is likely to give you recursion level errors (as each update will retrigger).

• If I were in your situation, I'd rename the underlying table

nvtest => nvtest_actual.

• Create a view on nvtest_actual, called nvtest.

• Write an instead of trigger on it, which does the mapping.

create table nvtest (

  cliref number,

  addrtype varchar2(400),

  mobile number

);

insert into nvtest values (900, 'CORR' , 7412);

insert into nvtest values (900, 'RESID' , 7401668992);

alter table nvtest rename to nvtest_actual;

create view nvtest as

  select *

  from  nvtest_actual;

create or replace trigger nvtesttrigger instead of update on nvtest

for each row

begin

  update nvtest_actual

  set mobile = :new.mobile

  where  addrtype = case :old.addrtype

                    when 'CORR' then 'RESID'

                    when 'RESID' then 'CORR'

                    else :old.addrtype

                  end and cliref = :old.cliref;

end;

/

update nvtest

set mobile = '123'

where cliref = 900 and addrtype = 'RESID';

select * from nvtest;

The table description you provided at the beginning is not good.

You probably thought something like this:

create table nvtest (

  ref number,

  addr_type varchar2(10),

  addr varchar2(40),

  mobile varchar2(20),

  constraint nvtest_addr_type_ck check (addr_type in ('RESID', 'CORR')),

  constraint nvtest_ref_addr_type_uk unique (ref, addr_type)

)

/

insert into nvtest values (1, 'RESID', 'addr 1', '11111');

insert into nvtest values (1, 'CORR',  'addr 2', '11111');

insert into nvtest values (2, 'RESID', 'addr 3', '22222');

insert into nvtest values (2, 'CORR',  'addr 4', '22222');

insert into nvtest values (3, 'RESID', 'addr 5', '33333');

insert into nvtest values (3, 'CORR',  'addr 6', '33333');

commit;

The question is whether the CORR and RESID rows must always be in pairs?

If so, it's pretty hard to solve this by code on the database!

It would be better to have such a table structure where everything is in one row:

create table nvtest (

  ref number,

  addr_RESID varchar2(40),

  addr_CORR varchar2(40),

  mobile varchar2(20),

  constraint nvtest_ref_uk unique (ref)

)

/

However, in your case, it is possible to make a (standard) solution to resolve trigger mutation, using a PL/SQL package and BUS / BUR / AUS triggers.

Unlike (standard) solutions, the package should contain another global variable / function (called IN_RECURSION, for example), which would initially be set to FALSE.

The variable would be set to TRUE in the AUS trigger before UPDATE, and then returned to FALSE.

All triggers would first test the value of that variable, and would not do anything if it had a TRUE value.

CREATE OR REPLACE PACKAGE plsql_table IS

  FUNCTION in_recursion RETURN BOOLEAN;

  PROCEDURE clear;

  PROCEDURE populate_with_uk (

    p_ref nvtest.ref%TYPE,

    p_addr_type nvtest.addr_type%TYPE,

    p_mobile nvtest.mobile%TYPE

  );

  PROCEDURE update_pair;

END;

/

CREATE OR REPLACE TRIGGER bus_nvtest

  BEFORE UPDATE ON nvtest

BEGIN

  IF NOT plsql_table.in_recursion THEN

    plsql_table.clear;

  END IF;

END;

/

CREATE OR REPLACE TRIGGER bur_nvtest

  BEFORE UPDATE ON nvtest

  FOR EACH ROW

BEGIN

  IF NOT plsql_table.in_recursion THEN

    IF :NEW.ref != :OLD.ref OR :NEW.addr_type != :OLD.addr_type THEN

       RAISE_APPLICATION_ERROR (-20001, 'BLA BLA ...');

    END IF;

    plsql_table.populate_with_uk (:OLD.ref, :OLD.addr_type, :NEW.mobile);

  END IF;

END;

/

CREATE OR REPLACE TRIGGER aus_nvtest

  AFTER UPDATE ON nvtest

BEGIN

  IF NOT plsql_table.in_recursion THEN

    plsql_table.update_pair;

  END IF;

END;

/

CREATE OR REPLACE PACKAGE BODY plsql_table IS

  -- prefiks m_ is for module level variables (defined in package body)

  -- prefiks g_ is for global variables (defined in package specification)

  m_in_recursion BOOLEAN := FALSE;

  m_rec_number BINARY_INTEGER;

  TYPE rec_t IS RECORD (

    ref nvtest.ref%TYPE,

    addr_type nvtest.addr_type%TYPE,

    mobile nvtest.mobile%TYPE

  );

  TYPE type_plsql_table IS TABLE OF rec_t INDEX BY BINARY_INTEGER;

  m_plsql_table type_plsql_table;

  FUNCTION in_recursion RETURN BOOLEAN IS

  BEGIN

    RETURN m_in_recursion;

  END;

  PROCEDURE clear IS

  BEGIN

    m_rec_number := 0;

  END;

  PROCEDURE populate_with_uk (

    p_ref nvtest.ref%TYPE,

    p_addr_type nvtest.addr_type%TYPE,

    p_mobile nvtest.mobile%TYPE

  ) IS

  BEGIN

    m_rec_number := m_rec_number + 1;

    m_plsql_table(m_rec_number).ref := p_ref;

    m_plsql_table(m_rec_number).addr_type := p_addr_type;

    m_plsql_table(m_rec_number).mobile := p_mobile;

  END;

  PROCEDURE update_pair IS

    v_ref nvtest.ref%TYPE;

    v_addr_type nvtest.addr_type%TYPE;

    v_mobile nvtest.mobile%TYPE;

  BEGIN

    m_in_recursion := TRUE;

    FOR i IN 1..m_rec_number LOOP

      v_ref       := m_plsql_table(i).ref;

      v_addr_type := m_plsql_table(i).addr_type;

      v_mobile    := m_plsql_table(i).mobile;

      UPDATE nvtest

         SET mobile = v_mobile

       WHERE ref = v_ref

         AND addr_type =

               CASE v_addr_type

                 WHEN 'CORR' THEN 'RESID'

                 ELSE 'CORR'

               END;

    END LOOP;

    m_in_recursion := FALSE;

  END;

END;

/

Regards,

Zlatko

It wont fix you problem but you should remove this bit of code:

EXCEPTION

   WHEN OTHERS

   THEN

      RAISE;

END;

All that will do is change the error stack so that when it reports which line of code caused the error it will point to the RAISE statement rather than the line of code that actually errored out.

You should never write EXCEPTION WHEN OTHERS unless you are going to do something useful in the handler (logging maybe, closing open files if you're using utl_file). Raise on it's own isn't useful since you'll get the same effect (with the correct error stack) if you just leave the exception handler out completely.