Mutating triggers & autonomous transactions
Date: Wed, 22 Apr 2009 12:47:23 -0700 (PDT)
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
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
in the underlying tables.
I think if you have this problem of mutating tables you solve it by
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
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.
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
FUNCTION CalcTotalOrderAmount( p_ order_header_id IN NUMBER) RETURN NUMBER
WHERE order_header_id = p_order_header_id;
My solution would be Received on Wed Apr 22 2009 - 14:47:23 CDT