Re: Mutating triggers & autonomous transactions
From: Shakespeare <whatsin_at_xs4all.nl>
Date: Thu, 23 Apr 2009 07:17:01 +0200
Message-ID: <49eff9c9$0$185$e4fe514c_at_news.xs4all.nl>
roelof.streekstra_at_gmail.com schreef:
>> roelof.streeks..._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
Date: Thu, 23 Apr 2009 07:17:01 +0200
Message-ID: <49eff9c9$0$185$e4fe514c_at_news.xs4all.nl>
roelof.streekstra_at_gmail.com schreef:
> On Apr 22, 4:16 pm, Shakespeare <what..._at_xs4all.nl> wrote:
>> roelof.streeks..._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
> > I understand what you say here but my question is can you use > autonomous transactions > in triggers to prevent the mutating table problem.
In that case, your assumption is correct.
Shakespeare Received on Thu Apr 23 2009 - 00:17:01 CDT