Re: Mutating triggers & autonomous transactions
From: joel garry <joel-garry_at_home.com>
Date: Wed, 22 Apr 2009 17:56:16 -0700 (PDT)
Message-ID: <02dcc8a3-5fcb-4c11-b20a-e0e8a365fb98_at_s1g2000prd.googlegroups.com>
On Apr 22, 3:23 pm, roelof.streeks..._at_gmail.com wrote:
> 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.
Date: Wed, 22 Apr 2009 17:56:16 -0700 (PDT)
Message-ID: <02dcc8a3-5fcb-4c11-b20a-e0e8a365fb98_at_s1g2000prd.googlegroups.com>
On Apr 22, 3:23 pm, roelof.streeks..._at_gmail.com wrote:
> 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.
Search for the word mutating at asktom.oracle.com, and read his books. And look for "red flags" here: http://tkyte.blogspot.com/2008/05/another-of-day.html
jg
-- _at_home.com is bogus. http://blog.wired.com/27bstroke6/2009/04/pins.htmlReceived on Wed Apr 22 2009 - 19:56:16 CDT