Practical Oracle 8i - Building Efficient Databases

Chapter 20: Autonomous Transactions.

Addenda

Errata

There is a typo on page 453 in the
code section for the after row insert trigger. Where the code
reads:

where invoice_id =:new.invoice_no;

it should read

where invoice_id =:new.invoice_id;

Thanks to Cory Brooks (coryb@nc.rr.com)for
pointing this out to me. Cory also raised the question - since I
had created a function curr_value in my
sequences package, why didn't I use that instead of :new.invoice_id
?

Of course, I could have used the function, but in this case it
was more efficient to use the existing variable rather than
calling a PL/SQL function. (Note that Oracle's internal currval
is also much more efficient than a PL/SQL function call). I
included the unused curr_value function
simply because it seemed an obvious useful little extra. It has
also occurred to me that you might want a 'previous value'
function as well, which could be implemented very easily as
follows:

Add a variable seq_prev to the package.

In the function next_value add the
line seq_prev := seq_last; immediately
after the begin.