Forum Stats

  • 3,827,745 Users
  • 2,260,818 Discussions
  • 7,897,364 Comments

Discussions

After insert trigger mutating error,what is the way to overcome it.

2789666
2789666 Member Posts: 20
edited Jul 2, 2015 3:30PM in SQL & PL/SQL

I have two tables namely profiles_answers and user_profileanswers. Based on the requirement that is: when a user inserts answer in the user_profileanswers table i need to calculate the weight-age of that particular question by counting the no.of options available in the profiles_answers table and update this weight-age in the user_profileanswers table. So for this I have written the following After Insert trigger. But when I try to insert it is throwing me the mutating error. Since I'm updating the same table which is used for the Insert action in the trigger. Please let me know how can I overcome this.

create or replace

TRIGGER AI_weightageCaluculation AFTER

  INSERT ON user_profileanswers FOR EACH row

  BEGIN

  DECLARE

  v_a VARCHAR2(50);

  v_b VARCHAR2(50);

  v_c VARCHAR2(50);

  v_d VARCHAR2(50);

  v_e VARCHAR2(50);

  a_weightage NUMBER;

  b_weightage NUMBER;

  c_weightage NUMBER;

  d_weightage NUMBER;

  e_weightage NUMBER;

  BEGIN

  SELECT option_a,option_b,option_c, option_d,option_e INTO

  v_a, v_b, v_c, v_d, v_e   FROM profiles_answers

  WHERE profile_questions_id = :new.profilequestion_id;

  IF (v_a  IS NOT NULL AND v_b IS NOT NULL AND v_c IS NOT NULL AND v_d IS NOT NULL AND v_e IS NOT NULL) THEN

  BEGIN

  a_weightage := 85;

  b_weightage := 60;

  c_weightage := 45;

  d_weightage := 30;

  e_weightage := 15;

  END;

  ELSIF (v_a IS NOT NULL AND v_b IS NOT NULL AND v_c IS NOT NULL AND v_d IS NOT NULL AND v_e IS NULL) THEN

  BEGIN

  a_weightage := 85;

  b_weightage := 60;

  c_weightage := 30;

  d_weightage := 15;

  END;

  ELSIF (v_a IS NOT NULL AND v_b IS NOT NULL AND v_c IS NOT NULL AND v_d IS NULL AND v_e IS NULL) THEN

  BEGIN

  a_weightage := 85;

  b_weightage := 45;

  c_weightage := 15;

  END;

  ELSE

            BEGIN

  a_weightage := 85;

  b_weightage := 15;

            END;

  END IF;

  IF :new.answer = 'A' THEN

            BEGIN

  UPDATE user_profileanswers

  SET weightage          = a_weightage

  WHERE user_id          = :new.user_id

  AND profileanswer_id   = :new.profileanswer_id

  AND profilequestion_id = :new.profilequestion_id;

            END;

  ELSIF :new.answer = 'B' THEN

            BEGIN

  UPDATE user_profileanswers

                SET weightage          = b_weightage

                WHERE user_id          = :new.user_id

                AND profileanswer_id   = :new.profileanswer_id

                AND profilequestion_id = :new.profilequestion_id;

            END;

            ELSIF :new.answer = 'C' THEN

            BEGIN

  UPDATE user_profileanswers

                SET weightage          = c_weightage

                WHERE user_id          = :new.user_id

                AND profileanswer_id   = :new.profileanswer_id

                AND profilequestion_id = :new.profilequestion_id;

            END;

            ELSIF :new.answer = 'D' THEN

            BEGIN

  UPDATE user_profileanswers

                SET weightage          = d_weightage

                WHERE user_id          = :new.user_id

                AND profileanswer_id   = :new.profileanswer_id

                AND profilequestion_id = :new.profilequestion_id;

            END;

            ELSE

            BEGIN

  UPDATE user_profileanswers

                SET weightage          = e_weightage

                WHERE user_id          = :new.user_id

                AND profileanswer_id   = :new.profileanswer_id

                AND profilequestion_id = :new.profilequestion_id;

            END;

            END IF;

        END;

  END;

Thanks in advance.

Tagged:
BluShadowChris Hunt2789666John Stegeman

Best Answer

  • RogerT
    RogerT Member Posts: 1,858 Gold Trophy
    edited Jul 2, 2015 4:10AM Answer ✓

    hmm...why doing it after insert?

    CREATE OR REPLACE TRIGGER BI_weightageCaluculation

       BEFORE INSERT ON user_profileanswers FOR EACH ROW

    BEGIN

        SELECT CASE :NEW.ANSWER

                  WHEN 'A' THEN a_weight

                  WHEN 'B' THEN b_weight

                  WHEN 'C' THEN c_weight

                  WHEN 'D' THEN d_weight

                  ELSE e_weight

               END

          into :new.weightage

          FROM (SELECT 85 a_weight

                    ,CASE option_result

                        WHEN 31 THEN 60

                        WHEN 15 THEN 60

                        WHEN 7  THEN 45

                        else 15

                     end b_weight

                    ,CASE option_result

                        WHEN 31 THEN 45

                        WHEN 15 THEN 30

                        when 7  then 15

                     END c_weight

                    ,CASE option_result

                        WHEN 31 THEN 30

                        when 15 then 15

                     END d_weight

                    ,CASE option_result

                        WHEN 31 THEN 15

                     END e_weight

                 FROM (SELECT DECODE(option_a,NULL,0,1) +

                              DECODE(option_b,NULL,0,2) +

                              DECODE(option_c,NULL,0,4) +

                              DECODE(option_d,NULL,0,8) +

                              DECODE(option_e,NULL,0,16) option_result

                         FROM profiles_answers

                        WHERE profile_questions_id = :new.profilequestion_id));

    END;

    /

    hth

    27896662789666
«1

Answers

  • Utsav
    Utsav Member Posts: 859 Silver Badge
    edited Jul 2, 2015 2:19AM

    Change it to pragma autonomous transaction and If possible change it also to statement level

    2789666
  • 2789666
    2789666 Member Posts: 20
    edited Jul 2, 2015 2:24AM

    Tried pragma autonomous transaction method. The result is like this when I try to insert a value,

    Error starting at line 3 in command:

    insert into user_profileanswers (user_id,profileanswer_id,profilequestion_id,status,answer) values (1,2,24,1,'B')

    Error report:

    SQL Error: ORA-06519: active autonomous transaction detected and rolled back

    ORA-06512: at "AI_WEIGHTAGECALUCULATION", line 89

    ORA-04088: error during execution of trigger 'AI_WEIGHTAGECALUCULATION'

    06519. 00000 -  "active autonomous transaction detected and rolled back"

    *Cause:    Before returning from an autonomous PL/SQL block, all autonomous

               transactions started within the block must be completed (either

               committed or rolled back). If not, the active autonomous

               transaction is implicitly rolled back and this error is raised.

    *Action:   Ensure that before returning from an autonomous PL/SQL block,

               any active autonomous transactions are explicitly committed

               or rolled back.

  • Utsav
    Utsav Member Posts: 859 Silver Badge
    edited Jul 2, 2015 2:34AM

    Each autonomous procedure will require an explicit commit or rollback.

    Please put commit at the end of your processing in the trigger.

  • 2789666
    2789666 Member Posts: 20
    edited Jul 2, 2015 2:54AM

    Thanks I did the commit part and its done...

  • Paul  Horth
    Paul Horth Member Posts: 3,402 Gold Trophy
    edited Jul 2, 2015 3:42AM

    No, no, NO!

    If you do that, your trigger will commit but you have no idea if the original insert commits or rolls back. If it rolls back you

    are changing data based on something that didn't happen. The trigger can also fire multiple times.

    Please read

    BluShadowJohn Stegeman
  • RogerT
    RogerT Member Posts: 1,858 Gold Trophy
    edited Jul 2, 2015 4:10AM Answer ✓

    hmm...why doing it after insert?

    CREATE OR REPLACE TRIGGER BI_weightageCaluculation

       BEFORE INSERT ON user_profileanswers FOR EACH ROW

    BEGIN

        SELECT CASE :NEW.ANSWER

                  WHEN 'A' THEN a_weight

                  WHEN 'B' THEN b_weight

                  WHEN 'C' THEN c_weight

                  WHEN 'D' THEN d_weight

                  ELSE e_weight

               END

          into :new.weightage

          FROM (SELECT 85 a_weight

                    ,CASE option_result

                        WHEN 31 THEN 60

                        WHEN 15 THEN 60

                        WHEN 7  THEN 45

                        else 15

                     end b_weight

                    ,CASE option_result

                        WHEN 31 THEN 45

                        WHEN 15 THEN 30

                        when 7  then 15

                     END c_weight

                    ,CASE option_result

                        WHEN 31 THEN 30

                        when 15 then 15

                     END d_weight

                    ,CASE option_result

                        WHEN 31 THEN 15

                     END e_weight

                 FROM (SELECT DECODE(option_a,NULL,0,1) +

                              DECODE(option_b,NULL,0,2) +

                              DECODE(option_c,NULL,0,4) +

                              DECODE(option_d,NULL,0,8) +

                              DECODE(option_e,NULL,0,16) option_result

                         FROM profiles_answers

                        WHERE profile_questions_id = :new.profilequestion_id));

    END;

    /

    hth

    27896662789666
  • Chris Hunt
    Chris Hunt Member Posts: 2,066 Gold Trophy
    edited Jul 2, 2015 4:11AM

    If the row you are trying to update is the one that's firing the trigger, change it to a BEFORE INSERT trigger and change statements like

      UPDATE user_profileanswers
      SET weightage          = a_weightage
      WHERE user_id          = :new.user_id
      AND profileanswer_id   = :new.profileanswer_id
      AND profilequestion_id = :new.profilequestion_id;
    

    To just

    :new.weightage := a_weightage
    
  • BluShadow
    BluShadow Member, Moderator Posts: 41,978 Red Diamond
    edited Jul 2, 2015 4:16AM

    Completely agree with Paul Horth, using autonomous transactions in triggers is WRONG.... and in case you didn't get that... it's WRONG!.....yes WRONG  (repeat until you believe it!)

    You are trying to implement some business logic and transactional processing as part of a trigger.  That is generally considered an abuse of triggers i.e. you are using a trigger for the wrong reason.  Such business logic should be applied within the application code, such that you insert your data (and maybe have a trigger to create the primary key as triggers are useful for), and then your code updates data (on the same table or others) as necessary, and then commits all of that work as a single transaction when it's complete.

    Trying to palm off the business code into a trigger so that it happens after the insert is just wrong (yes WRONG!) when it's perfectly placed to appear in your main code, after you've done the insert.

    2789666John Stegeman
  • sgalaxy
    sgalaxy Member Posts: 5,708 Bronze Trophy
    edited Jul 2, 2015 4:36AM

    Hi,

    I paste below an excerpt from Tom Kyte:

    "... in 999 times out of 1000, if you find yourself "forced" to use an autonomous transaction - it likely means you have a serious data integrity issue you haven't thought about.

    Where do people try to use them?

    • in that trigger that calls a procedure that commits (not an error logging routine). Ouch, that has to hurt when you rollback.
    • in that trigger that is getting the mutating table constraint. Ouch, that hurts *even more*

    Error logging - OK.

    Almost everything else - not OK."

    Chris Hunt
  • 2789666
    2789666 Member Posts: 20
    edited Jul 2, 2015 6:27AM

    Thank you, I understood the mistake and done the changes as you said... Thanks..:)

This discussion has been closed.