Results 1 to 12 of 12
  1. #1
    Join Date
    Mar 2009
    Posts
    3

    Unanswered: maximum number of recursive SQL levels (50) exceeded

    Hi, I am getting below error in trigger execution.Can you tell me the reason why this is happening.Can you describe more.How to resolve it...

    java.sql.SQLException: ORA-00036: maximum number of recursive SQL levels (50) exceeded
    ORA-06512: at "LOIS.STUDENTOBJECTACCESS_TRIG", line 4
    ORA-04088: error during execution of trigger 'LOIS.STUDENTOBJECTACCESS_TRIG'
    ORA-06512: at "LOIS.SOL_ACCESSGROUPTRIG1", line 27
    ORA-04088: error during execution of trigger 'LOIS.SOL_ACCESSGROUPTRIG1'
    ORA-06512: at "LOIS.PROGRAMROLLUP", line 421
    ORA-06512: at "LOIS.SOL_PROGRAMROLLUP_TRIG2", line 2
    ORA-04088: error during execution of trigger 'LOIS.SOL_PROGRAMROLLUP_TRIG2'
    ORA-06512: at "LOIS.PROGRAMROLLUP", line 421
    ORA-06512: at "LOIS.SOL_PROGRAMROLLUP_TRIG2", line 2
    ORA-04088: error during execution of trigger 'LOIS.SOL_PROGRAMROLLUP_TRIG2'
    ORA-06512: at "LOIS.PROGRAMROLLUP", line 421
    ORA-06512: at "LOIS.SOL_PROGRAMROLLUP_TRIG2", line 2
    ORA-04088: error during execution of trigger 'LOIS.SOL_PROGRAMROLLUP_TRIG2'
    ORA-06512: at "LOIS.PROGRAMROLLUP", line 421
    ORA-06512: at "LOIS.SOL_PROGRAMROLLUP_TRIG2", line 2
    ORA-04088: error during execution of trigger 'LOIS.SOL_PROGRAMROLLUP_TRIG2'
    ORA-0651

    Thanks,
    Mohsin

  2. #2
    Join Date
    Dec 2003
    Posts
    1,074
    We've gotten these when we've had a row-level trigger and an after-statement trigger on the same table. From the after-statement trigger, we'd fire off an insert/update/delete which would trigger the row-level trigger, which would trigger the after-statement trigger, and round & round it would go until it hits this error.

    You have to put some code in the after-statement trigger so that it can tell the difference between the outer-most SQL statement being executed, vs those being executed from the after-statemet trigger itself. We use a variable in a PACKAGE SPEC to do this, which basically helps us track the state of the process:

    Code:
    If called_from_at_trigger then
      skip DML statement;
    end;
    --=Chuck

  3. #3
    Join Date
    Nov 2009
    Posts
    5
    Could you please give a clearer example for putting this check ? i.e. how to check for a call from a row-level trigger

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Can you post the TRIGGER DDL?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    Nov 2009
    Posts
    5
    This is the trigger code -
    --------------------------------------------------------------------
    create or replace
    TRIGGER NOMRDATA_CMPD_TRIG
    FOR UPDATE OF STATUS ON NOMRDATATRIGGER_TEST
    COMPOUND TRIGGER

    TYPE t_change_tab IS TABLE OF NOMRDATATRIGGER_TEST%ROWTYPE;
    g_change_tab t_change_tab := t_change_tab();

    AFTER EACH ROW IS

    BEGIN
    g_change_tab.extend;
    g_change_tab(g_change_tab.last).status := :NEW.status;
    g_change_tab(g_change_tab.last).registryno := :NEW.registryno;

    END AFTER EACH ROW;

    AFTER STATEMENT IS

    BEGIN

    FORALL i IN g_change_tab.first .. g_change_tab.last

    UPDATE nomrdatatrigger_test set status = 'R' where registryno = g_change_tab(i).registryno and status = 'A';

    g_change_tab.delete;

    END AFTER STATEMENT;

    END NOMRDATA_CMPD_TRIG;

    -----------------------------------------------------------------------

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    can you change that for each row and just do a set based update instead?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  7. #7
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    I'm probably missing something, but doesn't this trigger achieve the same as yours attempts to?
    Code:
    create or replace
    TRIGGER NOMRDATA_CMPD_TRIG
    BEFORE UPDATE OF STATUS ON NOMRDATATRIGGER_TEST
    BEGIN
       IF :NEW.status = 'A' THEN
          :NEW.status := 'R'
       END IF;
    NOMRDATA_CMPD_TRIG;

  8. #8
    Join Date
    Nov 2009
    Posts
    5
    In that case wouldnt I get a "mutating table" error ?

  9. #9
    Join Date
    Nov 2009
    Posts
    5
    The trigger should not update the current row that is in question, but all others, this is the updated code --

    ______________________________________________

    create or replace
    TRIGGER NOMRDATA_CMPD_TRIG2
    FOR UPDATE OF STATUS ON NOMRDATATRIGGER_TEST2
    COMPOUND TRIGGER

    TYPE t_change_tab IS TABLE OF NOMRDATATRIGGER_TEST2%ROWTYPE;
    g_change_tab t_change_tab := t_change_tab();

    AFTER EACH ROW IS

    BEGIN
    g_change_tab.extend;
    g_change_tab(g_change_tab.last).status := :NEW.status;
    g_change_tab(g_change_tab.last).registryno := :NEW.registryno;
    g_change_tab(g_change_tab.last).roid := :NEW.rowid;
    END AFTER EACH ROW;

    AFTER STATEMENT IS

    BEGIN

    FORALL i IN g_change_tab.first .. g_change_tab.last

    UPDATE nomrdatatrigger_test2 set status = 'R' where
    registryno = g_change_tab(i).registryno and status = 'A'
    and rowid <> g_change_tab(i).roid;

    g_change_tab.delete;

    END AFTER STATEMENT;

    END NOMRDATA_CMPD_TRIG2;

    _________________________________________________

  10. #10
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    I think you need to stop the updates that the trigger itself causes then firing the trigger again - something like this:
    Code:
    create or replace
    TRIGGER NOMRDATA_CMPD_TRIG2
    FOR UPDATE OF STATUS ON NOMRDATATRIGGER_TEST2
    COMPOUND TRIGGER
    
    TYPE t_change_tab IS TABLE OF NOMRDATATRIGGER_TEST2%ROWTYPE;
    g_change_tab t_change_tab := t_change_tab();
    g_cascade BOOLEAN := TRUE;
    
    AFTER EACH ROW IS
    
    BEGIN
     IF g_cascade THEN
      g_change_tab.extend;
      g_change_tab(g_change_tab.last).status := :NEW.status;
      g_change_tab(g_change_tab.last).registryno := :NEW.registryno;
      g_change_tab(g_change_tab.last).roid := :NEW.rowid;
     END IF;
    END AFTER EACH ROW;
    
    AFTER STATEMENT IS
    
    BEGIN
    
     g_cascade := FALSE;
    
     FORALL i IN g_change_tab.first .. g_change_tab.last
    
      UPDATE nomrdatatrigger_test2 set status = 'R' where
      registryno = g_change_tab(i).registryno and status = 'A'
      and rowid <> g_change_tab(i).roid;
    
     g_change_tab.delete;
    
    END AFTER STATEMENT;
    
    END NOMRDATA_CMPD_TRIG2;

  11. #11
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by rajeshJillella View Post
    The trigger should not update the current row that is in question, but all others

    What does that mean?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  12. #12
    Join Date
    Nov 2009
    Posts
    5
    Let me post the actual requirement, that would be better to understand -

    ================================================== ======
    Create trigger on orsdev on “regdb_normdata” table on update “status” column to value “D”.
    The trigger should take the “registryno” value from the updated row and update any other rows on regdb_normdata table that have the same registryno and status ‘A” and set the status for those rows to “R”. The row that is being updated with status “D” should not be updated with status “R”.
    ================================================== =======

    So, what I meant was the current row that is being updated should not be updated again by the trigger which is fired due to the current update !

Posting Permissions

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