Results 1 to 2 of 2
  1. #1
    Join Date
    Feb 2008
    Posts
    26

    Cool Unanswered: How to solve a recursivity error caused by delete cascade

    Hi everybody !

    For a start, I detail the difference between executions of triggers when deleting in a parent table linked by cascade deletion in the 2 famous DBMS : Oracle (9i version for me) and Ms Sql Server.

    When is the after delete trigger launched?
    SQL SERVER: after each DELETE instruction from the table
    ORACLE 9i: only when there is really something to remove in the table

    The functioning of the cascade deletion
    SQL SERVER: the record in the child table linked to the deleted record in the parent one will be removed and so on ...
    ORACLE 9i: even if there is no corresponding record in the child tables, the cascade mechanism travels all over the linked tables and the trigger in the last table will be launched!!

    I get the following error when trying to delete an inexisting record
    ex: delete from t_ob_entity where id = null

    ERREUR la ligne 1 :
    ORA-00036: maximum number of recursive SQL levels (50) exceeded
    ORA-00036: maximum number of recursive SQL levels (50) exceeded
    ORA-00036: maximum number of recursive SQL levels (50) exceeded
    ORA-06512: at "MECHANICSQL.A_D_T_LNK_GROUPS_PINS", line 7
    ORA-04088: error during execution of trigger 'MECHANICSQL.A_D_T_LNK_GROUPS_PINS'
    ORA-06512: at "MECHANICSQL.A_D_T_LNK_GROUPS_PINS", line 17
    ORA-04088: error during execution of trigger 'MECHANICSQL.A_D_T_LNK_GROUPS_PINS'
    ORA-06512: at "MECHANICSQL.A_D_T_LNK_GROUPS_PINS", line 17
    ORA-04088: error during execution of trigger 'MECHANICSQL.A_D_T_LNK_GROUPS_PINS'
    ORA-06512: at "MECHANICSQL.A_D_T_LNK_GROUPS_PINS", line 17
    ORA-04088: error during execution of trigger 'MECHANICSQL.A_D_T_LNK_GROUPS_PINS'
    ORA-06512: at "MECHANICSQL.A_D_T_LNK_GROUPS_PINS", line 17
    ORA-04088: error during execution of trigger 'MECHANICSQL.A_D_T_LNK_GROUPS_PINS'
    ORA-06512: at "MECHANICSQL.A_D_T_LNK_GROUPS_PINS", line 17
    ORA-04088: error during execution of trigger 'MECHA


    Why do the cascade relations checked and yet there is no record to delete ? And why the after delete trigger of the last child table launched ?

    Here is the whole script of the database creation : base_test.sql.txt

  2. #2
    Join Date
    Feb 2008
    Posts
    26
    What do you think about oraclians ?? ;-)

Posting Permissions

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