Mutating triggers & autonomous transactions
Date: Wed, 22 Apr 2009 12:47:23 -0700 (PDT)
Message-ID: <960c7efc-995b-45ac-80f8-2d19de9f2e68_at_c36g2000yqn.googlegroups.com>
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
begin
....
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);
...
end;
Function code:
FUNCTION CalcTotalOrderAmount( p_ order_header_id IN NUMBER)
RETURN NUMBER
IS
RETVAL NUMBER;
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
SELECT SUM(price)
INTO RETVAL
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