Table is mutating - Oracle trigger

Here is the Oracle Trigger that causes the Table TSLINES is mutating error.
I am trying to get some values from another table but I must reference the TSLINES table to get them, so I though I could select the values and eliminate the row somehow so I could get rid of the error but does not work.  The line with the error is shown below and if I comment it out then the trigger works fine, but I need the other values to make this right.

This is the line shown in the trigger below
SELECT SUM(NVL(TSD_MIN,0)) INTO i_sumoth FROM TSDETAIL WHERE TSD_TSL <> :new.TSL_KEY AND TSD_TSL IN (SELECT TSL_KEY FROM TSLINES WHERE
TSL_TSH=:new.TSL_TSH AND TSL_CHR=:new.TSL_CHR AND TSL_KEY <> :new.TSL_KEY);

How can I do this is in a Trigger?  This is for a client and I have limited dba access etc so creating something else is going to be difficult if not impossible.

CREATE or REPLACE TRIGGER TSL_CURR_ETC_EAC
BEFORE INSERT OR UPDATE
    ON TSLINES
      REFERENCING NEW AS NEW OLD AS OLD
    FOR EACH ROW
DECLARE i_budget FLOAT;
        i_actual FLOAT;
        i_etc    INTEGER;
        i_res    INTEGER;
        i_cnt    INTEGER;
      s_rat    VARCHAR(20);
      v_fld1   VARCHAR(50);
      v_fld2   VARCHAR(70);
      v_fld3   VARCHAR(90);
      i_summin INTEGER;
      i_sumoth INTEGER;
BEGIN
      SELECT EMP_RES INTO i_res FROM EMPLOYEE,TSHEADER WHERE TSH_EMP=EMP_KEY AND TSH_KEY=:new.TSL_TSH;
      -- This has to be done every time and done first
      IF (:new.TSL_FLD9 IS NULL OR :new.TSL_CHR <> :old.TSL_CHR) THEN
            :new.TSL_FLD8 := 0;
            SELECT Count(*) INTO i_cnt FROM CHRRES WHERE CRS_CHR=:new.TSL_CHR AND CRS_RES=i_res;
            IF i_cnt > 0 THEN
                  SELECT CRS_ETC INTO i_etc FROM CHRRES WHERE CRS_CHR=:new.TSL_CHR AND CRS_RES=i_res;
                  :new.TSL_FLD9 := i_etc;
            ELSE
                  :new.TSL_FLD9 := -1;
            END IF;
      END IF;

      -- This clears certain columns when the Rate Code <> 'ST'
      SELECT RAT_CODE INTO s_rat FROM RATE WHERE RAT_KEY=:new.TSL_RAT;
      IF s_rat <> 'ST' THEN
            :new.TSL_FLD6 :=' ';
            :new.TSL_FLD7 := 0;
            :new.TSL_FLD8 := 0;
      ELSE
            -- This calculates and displays the EAC and Current ETC columns (might require change when COMPLETE flag is SET
            IF (:new.TSL_FLD6 = '' OR :new.TSL_FLD6 = ' ' OR :new.TSL_FLD6 IS NULL OR :new.TSL_CHR <> :old.TSL_CHR) THEN
        
                  :new.TSL_FLD6 := Concat(TO_CHAR(:new.TSL_ETC/60,9999),'h');
                  :new.TSL_FLD8 := 0;
                  IF :new.TSL_ETC = 0 or :new.TSL_ETC IS NULL then
                        :new.TSL_FLD6 :='  ';
                  END IF;
                  SELECT Count(*) INTO i_cnt FROM CHRRES WHERE CRS_CHR=:new.TSL_CHR AND CRS_RES=i_res;
                  IF i_cnt > 0 THEN
                        SELECT CRS_EWORK / 60, CRS_AWORK/ 60  INTO i_budget,i_actual FROM CHRRES WHERE CRS_CHR=:new.TSL_CHR AND CRS_RES=i_res;
                        IF i_budget IS NULL THEN
                              i_budget := 0;
                        END IF;
                        IF i_actual IS NULL THEN
                              i_actual :=0;
                        END IF;
                        :new.TSL_FLD7 := i_actual + :new.TSL_ETC/60;
                  ELSE
                        :new.TSL_FLD7 := 0;
                  END IF;
            END IF;
      END IF;
      IF :new.TSL_COMP='T' THEN
            :new.TSL_FLD8 := 0;
            :new.TSL_ETC  := 0;
      ELSE
            SELECT SUM(NVL(TSD_MIN,0)) INTO i_summin FROM TSDETAIL WHERE TSD_TSL=:new.TSL_KEY;

-- If I comment out this line the trigger works, except I don't get the results I want.
            SELECT SUM(NVL(TSD_MIN,0)) INTO i_sumoth FROM TSDETAIL WHERE TSD_TSL <> :new.TSL_KEY AND TSD_TSL IN (SELECT TSL_KEY FROM TSLINES WHERE
TSL_TSH=:new.TSL_TSH AND TSL_CHR=:new.TSL_CHR AND TSL_KEY <> :new.TSL_KEY);


            IF :new.TSL_FLD9 > 0 THEN
                  i_etc := (:new.TSL_FLD9 - (i_summin + i_sumoth));
                  IF i_etc < 0 THEN
                        i_etc := 0;
                  END IF;
                  IF i_etc = 0 THEN
                        :new.TSL_COMP :='T';
                        :new.TSL_ETC  := 0;
                  ELSE
                        :new.TSL_COMP :='F';
                        :new.TSL_ETC  := i_etc;
                  END IF;
            END IF;
            
      END IF;
      
      IF :new.TSL_FLD1 = '' OR :new.TSL_FLD1 = ' ' OR :new.TSL_FLD1 IS NULL THEN
            SELECT EMP_TSL1 INTO v_fld1 FROM EMPLOYEE,TSHEADER WHERE TSH_EMP=EMP_KEY AND TSH_KEY=:new.TSL_TSH;
            :new.TSL_FLD1 := v_fld1;
      END IF;

      IF :new.TSL_FLD2 = '' OR :new.TSL_FLD2 = ' ' OR :new.TSL_FLD2 IS NULL THEN
            SELECT EMP_TSL2 INTO v_fld2 FROM EMPLOYEE,TSHEADER WHERE TSH_EMP=EMP_KEY AND TSH_KEY=:new.TSL_TSH;
            :new.TSL_FLD2 := v_fld2;
      END IF;

      IF :new.TSL_FLD3 = '' OR :new.TSL_FLD3 = ' ' OR :new.TSL_FLD3 IS NULL THEN
            SELECT EMP_TSL3 INTO v_fld3 FROM EMPLOYEE,TSHEADER WHERE TSH_EMP=EMP_KEY AND TSH_KEY=:new.TSL_TSH;
            :new.TSL_FLD3 := v_fld3;
      END IF;
END;
hmstechsupportAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

MikeOM_DBACommented:
Try:
CREATE OR REPLACE VIEW tslines_vw
AS
  SELECT * FROM tslines;

Open in new window

and replace your query with the view:
SELECT SUM ( NVL (tsd_min, 0))
  INTO i_sumoth
  FROM tsdetail
 WHERE tsd_tsl <> :new.tsl_key
   AND tsd_tsl IN (SELECT tsl_key
                     FROM tslines_vw
                    WHERE tsl_tsh = :new.tsl_tsh
                      AND tsl_chr = :new.tsl_chr
                      AND tsl_key <> :new.tsl_key);

Open in new window


HTH
0
MikeOM_DBACommented:
Ooops, sorry.. only works on inserts.
0
MikeOM_DBACommented:
OK, here is how it works:
1) Create an autonomous function:

CREATE OR REPLACE FUNCTION get_sumoth 
   ( p_tsl_key VARCHAR2, p_tsl_tsh NUMBER, p_tsl_chr VARCHAR2)
  RETURN NUMBER
AS
  PRAGMA AUTONOMOUS_TRANSACTION;
  i_sumoth   NUMBER;
BEGIN
  SELECT SUM ( NVL (tsd_min, 0))
    INTO i_sumoth
    FROM tsdetail
   WHERE tsd_tsl <> p_tsl_key
     AND tsd_tsl IN (SELECT tsl_key
                       FROM tslines
                      WHERE tsl_tsh = p_tsl_tsh
                        AND tsl_chr = p_tsl_chr
                        AND tsl_key <> p_tsl_key);

  RETURN NVL (i_sumoth, 0);
END;
/

Open in new window

2) Make these changes to your trigger:
CREATE OR REPLACE TRIGGER tsl_curr_etc_eac
--
BEGIN
-- - - Etc...
/* Comment out this part
    SELECT SUM ( NVL (tsd_min, 0))
      INTO i_sumoth
      FROM tsdetail
     WHERE tsd_tsl <> :new.tsl_key
       AND tsd_tsl IN (SELECT tsl_key
                         FROM tslines
                        WHERE tsl_tsh = :new.tsl_tsh
                          AND tsl_chr = :new.tsl_chr
                          AND tsl_key <> :new.tsl_key);
*/
-- And replace by:
i_sumoth := get_sumoth ( :new.tsl_key, :new.tsl_tsh, :new.tsl_chr);

-- - - Etc ...
END;
/

Open in new window

PS: Replace the function parameters with correct data types.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

slightwv (䄆 Netminder) Commented:
>>1) Create an autonomous function:

You need to be VERY careful with autonomous transactions and triggers.  You need to understand what these do or it can lead to incorrect results.

They run outside of the current transaction so any uncommitted rows are not involved.  So if the trigger will be changing results that may be needed for a correct result inside the trigger, it will not work.


Back to the question.

Do you need the select for the IN clause?

Without knowing your data it will be hard to say for sure but can you not get the results from tsdetail just with what you have inside the trigger?

Guessing here but can you maybe use a CASE statement on the select on TSDETAIL based on the current TSLINES row values?

You might also look to denormalize TSLINES by adding a SUMMED value in TSDETAILS and have a trigger keep the SUMMED value in TSDETAIL up to date.
0
hmstechsupportAuthor Commented:
Thanks Mike.
Quick question on the Autonomous_Transaction.  Are there restrictions to using this?  I know that's a pretty loose question, so I guess I am wondering if I could do something like.

Transaction begins in application (I cannot change the application, but I know the order of the insert/updates)
INSERT or UPDATE the TSLINES row
INSERT OR UPDATE up to 7 TSDETAIL rows
UPDATE TSHEADER table with some totals

I'd like to use the Autonomous_Transaction to calculate the sums again and UPDATE some rows based on a calculation so I would call the Autonomous_Transaction (stored proc I think) in a update trigger of the TSHEADER and collect all the TSLINES rows unique by TSL_CHR in a cursor and then process them one at a time updating the TSLINES as I go.
Ideally I'd like to disable another trigger in TSLINES as I go as well and I think I can do that with an Execute Immediate statement.
Question 1: Is this possible?

The the application commits so the 2nd question is can I see these transactions in the Autonomous_Transaction

Commit Transaction
0
hmstechsupportAuthor Commented:
Thanks slightwv.  Your answer answers one of my last questions so I don't think this is going to work as I hoped as 'yes' I am dependent on the data in the applications transaction for my calculations so as an autonomous transaction the data has not yet been committed.  I cannot change the table structure or the application which is why I'm trying to do it through a trigger.

Thanks
0
johnsoneSenior Oracle DBACommented:
You say that you have limited ability to change things so this may not work for you, but here is the basic idea behind how I have done this in the past.

The part of the trigger that is causing the mutating table error, you remove.  In it's place you put an insert into a global temporary table (need to create one if you don't have one).  All you need to insert is the primary key.

Then create an after statement trigger (not after row trigger).  This trigger pulls the records from the global temporary table and does the part that you removed from the original trigger.  That kind of select is allowed in an after statement trigger.

Sometimes I have found that because of other triggers, you can get into a loop where the after statement trigger is firing another trigger that shouldn't fire again.  If that happens, you need to create a package (no body) that has a boolean variable that determines that you are in the after statement trigger.  The after statement trigger sets it to true and the other triggers need to check that, if true just exit because the work on that row has already been done.
0
hmstechsupportAuthor Commented:
All 3 answers make it possible for me to come up with a solution and further my understanding of Triggers in Oracle.
Thank you all.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.