Results 1 to 7 of 7
  1. #1
    Join Date
    Jun 2014
    Posts
    9

    Unanswered: table mutating on after triggers

    Hi, have next test structure
    Code:
    CREATE TABLE AFTER_TRIGGER_TEST_TBL_A
    (
      TBL_A_ID INTEGER CONSTRAINT PK_AFTER_TRIGGER_TEST_TBL_A  PRIMARY KEY
    );
    
    CREATE TABLE AFTER_TRIGGER_TEST_TBL_B
    (
      TBL_B_ID INTEGER CONSTRAINT PK_AFTER_TRIGGER_TEST_TBL_B  PRIMARY KEY
    );
    
    CREATE OR REPLACE TRIGGER AFTER_TRIGGER_TEST_TBL_A_ADIU1
    AFTER DELETE OR INSERT OR UPDATE
    ON AFTER_TRIGGER_TEST_TBL_A
    REFERENCING NEW AS NEW OLD AS OLD
    FOR EACH ROW
    BEGIN
      delete from AFTER_TRIGGER_TEST_TBL_B; 
    END AFTER_TRIGGER_TEST_TBL_A_ADIU1;
    
    CREATE OR REPLACE TRIGGER AFTER_TRIGGER_TEST_TBL_A_ADIU2
    AFTER DELETE OR INSERT OR UPDATE
    ON AFTER_TRIGGER_TEST_TBL_A
    REFERENCING NEW AS NEW OLD AS OLD
    FOR EACH ROW
    DECLARE
      CURSOR crCnt IS
         SELECT COUNT(*) AS CNT FROM
         AFTER_TRIGGER_TEST_TBL_B;
    BEGIN
      FOR cRec IN crCnt LOOP
        dbms_output.put_line(cRec.CNT);
          
      END LOOP; 
    END AFTER_TRIGGER_TEST_TBL_A_ADIU2;
    
    
    
    
    insert into AFTER_TRIGGER_TEST_TBL_B
      select level from dual
      connect by level < 10;
      
      
    commit;
    After that i run
    Code:
    insert into AFTER_TRIGGER_TEST_TBL_A 
    select level from dual
    connect by level < 3;
    
    rollback;
    And obtained dbms output:
    9
    0
    Why result is treated to be deterministic and no 'table is mutating' error occurs?

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    > delete from AFTER_TRIGGER_TEST_TBL_B;

    Why are you deleting every row from AFTER_TRIGGER_TEST_TBL_B?
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Jun 2014
    Posts
    9
    Quote Originally Posted by anacedent View Post
    > delete from AFTER_TRIGGER_TEST_TBL_B;

    Why are you deleting every row from AFTER_TRIGGER_TEST_TBL_B?
    It has no practical use. It's just testing.

  4. #4
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Quote Originally Posted by icegood
    Why result is treated to be deterministic and no 'table is mutating' error occurs?
    Because nothing is mutating in this scenario. However, this fails:
    Code:
    SQL> insert all
      2    into AFTER_TRIGGER_TEST_TBL_A values (100)
      3    into AFTER_TRIGGER_TEST_TBL_B values (200)
      4  select * from dual;
      into AFTER_TRIGGER_TEST_TBL_A values (100)
           *
    ERROR at line 2:
    ORA-04091: table SCOTT.AFTER_TRIGGER_TEST_TBL_B is mutating, trigger/function
    may not see it
    ORA-06512: at "SCOTT.AFTER_TRIGGER_TEST_TBL_A_ADIU2", line 3
    ORA-06512: at "SCOTT.AFTER_TRIGGER_TEST_TBL_A_ADIU2", line 6
    ORA-04088: error during execution of trigger
    'SCOTT.AFTER_TRIGGER_TEST_TBL_A_ADIU2'
    
    
    SQL>

  5. #5
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Duplicate post: http://www.dbforums.com/oracle/17030...ble-error.html

    Can a moderator please merge those two threads
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags: http://www.dbforums.com/misc.php?do=bbcode#code

    Tips for good questions:

    http://tkyte.blogspot.de/2005/06/how...questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html

  6. #6
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Similar - YES. Duplicate - not exactly.

  7. #7
    Join Date
    Jun 2014
    Posts
    9
    they are completely different. 1st one - about behaviour on before triggers (actually table mutating), another - about UB in after triggers, where 'table mutating' would be appreciated

Posting Permissions

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