3 Replies Latest reply: Dec 18, 2013 5:33 PM by davidp 2 RSS

    Problem with Trigger

    rkrish

      Hi

      I have a trigger below :

       

        create or replace trigger

          TR3_GBI_ACCT_SANITY  AFTER

              INSERT  ON ACCT_SANITY FOR EACH ROW

          DECLARE

              v_acct_type_cd  VARCHAR2(2);

          BEGIN

              SELECT ACCT_TYPE_CD

              INTO v_acct_type_cd

              FROM ACCT

              WHERE

                  ACCT_NBR = :new.ACCT_NBR;

         

              IF v_acct_type_cd = 'DT' THEN

                   ACCT_SANITY_MUTATE_PACKAGE.validate(

                                           :NEW.ACCT_NBR,

                                           :NEW:ACCT_STATUS_ID);

              END IF;

          END;

       

      and it uses a package :

       

      CREATE OR REPLACE PACKAGE ACCT_SANITY_MUTATE_PACKAGE AS

       

      PROCEDURE validate(  v_input_acct_nbr       IN     VARCHAR2,

                     v_input_status_id       IN     VARCHAR2 );

       

      END ACCT_SANITY_MUTATE_PACKAGE;

      /

       

      CREATE OR REPLACE PACKAGE BODY ACCT_SANITY_MUTATE_PACKAGE AS

       

      PROCEDURE validate(  v_input_acct_nbr       IN     VARCHAR2,

                     v_input_status_id       IN     VARCHAR2 ) is

              v_event_data_text          VARCHAR2(200);

              v_prev_status_id              VARCHAR2(3);

      BEGIN

       

          SELECT ACCT_STATUS_ID

          INTO v_prev_status_id

          FROM ACCT_SANITY

          WHERE

          ACCT_NBR = v_input_acct_nbr  AND ROWNUM = 1 ORDER BY ACCT_SANITY_EFF_DT DESC;

       

          IF ( ( v_input_status_id = 'N' ) or ( v_prev_status_id = 'N' and v_input_status_id != 'N' ) ) THEN

               BEGIN

                  v_event_data_text := ‘ACCT_NBR|’ || v_input_acct_nbr || ‘|SBSCRP_ID|’ || “”;

                          -- Create record in  BPP_QUEUE

                 INSERT INTO PGM_QUEUE

                 (SEQ_NBR,

                 ID,

                 TEXT,

                 STATUS_CD,

                 person_id,

                 ver_nbr)

                 VALUES

                 (to_char(CMD_SEQ_NBR.nextval),

                 'PD',

                  v_event_data_text,

                 'CR',

                 'AcctSanityTr1',

                 1);

                 EXCEPTION

                     WHEN others THEN

                         v_event_data_text := 'do nothing';

               END;

          END IF;

      END validate;

                      

      END ACCT_SANITY_MUTATE_PACKAGE;

      /

       

       

       

      When I'm trying to insert value in ACCT_SANITY table, It is throwing error like

       

       

      ERROR at line 1:

      ORA-04091: table PROJECT.ACCT_SANITY is mutating, trigger/function may not see

      it

      ORA-06512: at "PROJECT.ACCT_SANITY_MUTATE_PACKAGE", line 8

      ORA-06512: at "PROJECT.TR3_GBI_ACCT_SANITY", line 10

      ORA-04088: error during execution of trigger 'PROJECT.TR3_GBI_ACCT_SANITY'

       

       

      Can anyone please help me how to get rid of this error?

       

      Thanks in advance

        • 1. Re: Problem with Trigger
          Frank Kulash

          Hi,

           

          A FOR EACH ROW trigger on table acct_sanity can't query acct_sanity.  This applies to any procedures called from the trigger.

           

          The simplest way to fix the problem is to drop the trigger.

           

          The best way to do what you need depends on what you need.  Explain what your business requirements are, and someone will help you find a good way to meet them.

          Post a complete test case (as complete as you can make it) that people can run to re-create the problem and test their ideas.

          Describe exactly what you want to happen at each step.  For example:

          "I have 2 tables like these: CREATE TABLE ...

          Say they contain this data:  INSERT ...

          Now I want a trigger, such that if I do this:  INSERT INTO acct_sanity ...

          Then that should also change pgm_queue to contain this ... because ...

          However, if I do this: INSERT INTO acct_sanity ...

          then pgm_queue should not be changed, because ..."

          • 2. Re: Problem with Trigger
            Hoek

            The Mutating Table trigger error usually indicates a rather flawed design, and has been a FAQ for more than a decade.

            Please read these articles explaining what goes wrong and how to overcome the error:

            ORACLE-BASE - Oracle9i Mutating Table Exceptions

            Avoiding Mutating Tables

            Depending on your unknown DB version, you could opt for compound triggers:

            PL/SQL Triggers

             

            But first of all: make sure, verify that the design is correct for this requirement. Triggers are abused all the time.

            (Row level) Triggers don't think in sets. They slow you down, they'e causing context switches. Probably there is a better way, but be willing to do some reading until you understand.

            Also, please read:

            Ask Tom: The Trouble with Triggers

             

            Oh, one more thing:

            Why that WHEN OTHERS exception, without a RAISE in it??

            • 3. Re: Problem with Trigger
              davidp 2

              You'd probably be much better off building a stored procedure that does the full operation for a new ACCT_SANITY row (find the old value, insert the PGM_QUEUE row, insert the new ACCT_SANITY row) and calling that. IT's a much more straight forward, easily understood and maintainable approach (and I have done both, the trigger one because it was a software port maintaining backward compatibility).

               

              Your query to get the old value is flawed:

              SELECT ACCT_STATUS_ID

                  INTO v_prev_status_id

                  FROM ACCT_SANITY

                  WHERE

                  ACCT_NBR = v_input_acct_nbr  AND ROWNUM = 1 ORDER BY ACCT_SANITY_EFF_DT DESC;

              The ROWNUM=1 is applied before the order by, so any row for thje ACCT_NBR could be returned, depending on the optimiser's choice. Just because it gives the right result today doesn't mean it will next week. One correct way to get what you wanted is

              SELECT max(ACCT_STATUS_ID) keep (DENSE_RANK FIRST ORDER BY ACCT_SANITY_EFF_DT DESC NULLS LAST)

              FROM ACCT_SANITY

              WHERE  ACCT_NBR = v_input_acct_nbr