Forum Stats

  • 3,839,092 Users
  • 2,262,450 Discussions
  • 7,900,854 Comments

Discussions

Recursive trigger solution

naveenmani912
naveenmani912 Member Posts: 43 Red Ribbon
edited Apr 1, 2019 8:57AM in 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:

900CORR7412
900RESID7401668992

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;

/

EdStevensBilly VerreynneBEDEFrank KulashL. Fernigriniuser13328581

Answers

  • Paulzip
    Paulzip Member Posts: 8,720 Blue Diamond
    edited Mar 17, 2019 12:30PM

    Oracle version?

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 42,246 Red Diamond
    edited Mar 17, 2019 12:40PM

    Hi,

    Whenever you have a question, please post a little sample data (CREATE TABLE and INSERT statements, relevant columns only) for all the tables involved, and the exact results you want from that data, so that the people who want to help you can re-create the problem and test their ideas.

    If you're asking about a DML operation, such as UPDATE, then the INSERT statements you post should show what the tables are like before the DML, and the results will be the contents of the changed table after the DML.

    Explain, using specific examples, how you get those results from that data.

    Always say what version of Oracle you're using (e.g. 12.2.0.2.0).

    See the forum FAQ:

    Don't get requirements and solutions mixed up.  Requirements and solutions are related, but they're not the same thing.

    naveenmani912 wrote:Hi all I have a requirement to update same table using trigger. ...

    Is that really the requirement?  That is, did someone in your business tell you

    "Whenever we make a change to the nvtest table, we need to use a trigger to … ", or did they say

    "Whenever we change a customer's address we, need to … ", and you think a trigger on the nvtest table is one possible solution?

    EdStevensBilly Verreynneuser13328581
  • Paulzip
    Paulzip Member Posts: 8,720 Blue Diamond
    edited Mar 17, 2019 1:03PM

    I have to agree with Frank's comments.  The requirement is to update an alternate record, when an update occurs.  A trigger MAY be a solution, but there MAY be others.

    Anyway, you're getting a mutating table error because you are trying to reference the triggering table in DML from within row-level trigger code (in your case an update).  However, if you have 11g or above you can use compound triggers to prevent this.  Something like this (untested)...

    create or replace trigger nvtesttrigger

    for update on nvtest compound trigger

      type TRowTable is table of nvtest%rowtype;    

      vRowTable TRowTable := TRowTable();

      vRow nvtest%rowtype;   

      -- 

      after each row is   

      begin

        vRow.CliRef  := :old.CliRef;

        vRow.addrtype := case :old.addrtype

                       when 'CORR' then 'RESID'

                       when 'RESID' then 'CORR'

                       else :old.addrtype

                     end;

        vRow.Mobile := :new.mobile;

        vRowTable.Extend;

        vRowTable(vRowTable.Last) := vRow;

      end after each row;   

      -- 

      after statement is 

      begin     

        forall i in vRowTable.first..vRowTable.last

          update nvtest n

          set n.mobile = vRowTable(i).Mobile

          where n.addrtype = vRowTable(i).addrtype and n.cliref = vRowTable(i).cliref;

        vRowTable.delete;

      end after statement;   

    end;

    /

    BEDE
  • Paulzip
    Paulzip Member Posts: 8,720 Blue Diamond
    edited Mar 17, 2019 1:36PM

    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;

                             

    CLIREFADDRTYPEMOBILE
    900
    CORR
    123
    900
    RESID
    7401668992
    user13328581
  • naveenmani912
    naveenmani912 Member Posts: 43 Red Ribbon
    edited Mar 17, 2019 1:36PM
  • Zlatko Sirotic
    Zlatko Sirotic Member Posts: 2,134 Gold Trophy
    edited Mar 17, 2019 4:40PM

    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

  • Mustafa_KALAYCI
    Mustafa_KALAYCI Member Posts: 3,383 Bronze Crown
    edited Mar 18, 2019 4:06AM

    this design can give you a lot of headache! I am someone already hate triggers and if I were you I wouldn't write a trigger at all! I force development team does these updates from application. Also it would be nice to do update over a procedure (a procedure that gets all new values as parameters or a rowtype parameter) and make application call that procedure to run update rows. in that procedure you can add your update statements too. that way you don't have to write a trigger and run all requirements without mutating.

    even so, if changing application is not possible or there are so many applications that you can not even control, then I might try rename your table from nvtest to something like nvtest_table and create a view with name nvtest which is basically "select * from nvtest_table " and create an instead of update trigger on the view and run your updates on that trigger. so application(s) still be using nvtest but it will be a view.

    BEDE
  • Cookiemonster76
    Cookiemonster76 Member Posts: 3,410 Gold Trophy
    edited Mar 18, 2019 5:49AM

    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.

    Frank KulashL. Fernigrini
  • Zlatko Sirotic
    Zlatko Sirotic Member Posts: 2,134 Gold Trophy
    edited Apr 1, 2019 8:57AM

    Have you tried my solution (or someone else's solution)?

    You expect something else?