Mutating triggers & autonomous transactions

From: <>
Date: Wed, 22 Apr 2009 12:47:23 -0700 (PDT)
Message-ID: <>

Hi all,

I have an application that uses the pragma autonomous transactions inside trigger code to prevent the mutating triggers problem. I do not think you can use pragma autonomous transactions do this. Because an autonomous transactions is a new transaction with a new SCN.
To me oracle will execute the blow code like this:

Step 1 Create new orderline get SCN
Step 2 trigger fires
Step 3 inside trigger call function
Step 4 function is autonomous ei get second SCN

In my opinion time has elapsed between step 1 and step 4 so some other Process Could have gotten in between in those steps and change the data
in the underlying tables.

I think if you have this problem of mutating tables you solve it by creating
Three triggers on the underlying table.

   Trigger one is a before statement trigger to cleans out a temp    Table that stores the changed records of the table you are changing.

   Trigger Two is a after row trigger that fills up the temp table    With change records.
   Trigger three is a after statement trigger that does the work of    Calculating the total price of the order by using the temp table    and the underlying transactional table.

If you do the three trigger approach you never have two SCNís so you do
not have any problems with your read consistency ever.

Is my reasoning true/valid?

This approach of three triggers a learned longtime ago when oracle was still in the 7 series.

Thanks Roelof

PS I think that triggers should only be used for auditing and for implementing certain kind of check constraints.

Current Code pattern is like this:

    table Order_header ( id, cust#, TotalOrderAmount)     table Order_line (id,order_header_id,product_name,price)     trigger on T1 before row on delete, update, insert called T1_BRDUI     function CalcTotalOrderAmount( p_order_header_id)

trigger body T1_BRDUI

   UPDATE Order_header SET TotalOrderAmount=CalcTotalOrderAmount( NVL (:new.order_header_id,:old.order_header_id) WHERE id=NVL(:new.order_header_id,:old.order_header_id); ...

Function code:
FUNCTION CalcTotalOrderAmount( p_ order_header_id IN NUMBER)   RETURN NUMBER
  SELECT SUM(price)
    FROM Order_line
   WHERE order_header_id = p_order_header_id;

  RETURN retval;
END CalcTotalOrderAmount;

My solution would be Received on Wed Apr 22 2009 - 14:47:23 CDT

Original text of this message