Re: Before Insert Trigger

"Michael O'Shea" <michael.oshea_at_tessella.com> wrote in message news:1127894986.367955.180730_at_o13g2000cwo.googlegroups.com...

>> autonomous transaction (and they should generally
>> be avoided for performance reasons as well as
>> technical reasons).
>
> http://download-west.oracle.com/docs/cd/B10501_01/appdev.920/a96624/06_ora.htm#27440
>
> This doc has a section on the advantages of autonomous transactions.
> There is nothing on the disadvantages, the performance/technical
> disadvantages you allude to. Would it be possible to provide a citation
> or briefly elaborate?
>

Have you seen any Oracle documentation ever that describes the disadvantages of using a feature ?

Row-level triggers are undesirable because they reduce array-based processing to single row processing. (Although the feature is terrific because it ties processing directly to the data arrival)

Autonomous transactions are undesirable because they add a special overhead which is the creation of a separate transaction environment. (Although the feature may be very useful in certain special cases).

Combining row-level triggers and autonomous transactions CAN be very useful because it guarantees some data capture for any attempted row activity - but the side effects are: row-level processing, and creating a transactional environment on very row. Use only after careful deliberation.

On the plus side, the autonomous trigger commits whether or not the triggerin row operation commits or rolls back.

On the minus side, the autonomous trigger commits whether or not the triggerin row operation commits or rolls back.

See asktom.oracle.com for further examples, but a single DML statement may do an invisible, internal rollback to savepoint and restart. This means the autonomous triggers will have committed on the first pass, and will repeat on the restart. What might that do to your data integrity ?

Search asktom for "write consistency".

-- 
Regards

Jonathan Lewis

http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html
Cost Based Oracle - Volume 1: Fundamentals
On-shelf date: Nov 2005

http://www.jlcomp.demon.co.uk/faq/ind_faq.html
The Co-operative Oracle Users' FAQ

http://www.jlcomp.demon.co.uk/appearances.html
Public Appearances - schedule updated 4th Sept 2005