Re: Mutating triggers & autonomous transactions
From: Shakespeare <whatsin_at_xs4all.nl>
Date: Wed, 22 Apr 2009 22:16:35 +0200
Message-ID: <49ef7b1d$0$191$e4fe514c_at_news.xs4all.nl>
roelof.streekstra_at_gmail.com schreef:
> 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
>
>
I think you're right about this.
Date: Wed, 22 Apr 2009 22:16:35 +0200
Message-ID: <49ef7b1d$0$191$e4fe514c_at_news.xs4all.nl>
roelof.streekstra_at_gmail.com schreef:
> 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
>
>
I think you're right about this.
But it looks like something is missing at the end of your post.
If this is all about a total order amount, then this can be done more
simply. Just add the amount of an inserted row to the total; you don't
have to recalc the total every time. So you don't need to sum the detail
records to calculate the total. So you won't have a mutating table
problem at all.
Same can be done with updates and deletes. It's all a matter of good
book keeping.
An other option might be (not sure about that): lock the order header row before updating and calculating the total of the details. A second process can not get this lock so will not fire in the period you mentioned.
Shakespeare Received on Wed Apr 22 2009 - 15:16:35 CDT