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.
Create the function (remember to add it to the specification too):
function prev_value return number is begin return seq_prev; end;