Re: Auto generating Sequences !!

Brian Peasland wrote:
> Create the sequence outside of the trigger. If you want three different
> sequences, then create three sequences.
>
> In your trigger, you can get the sequence's next value with code similar
> to the following:
>
> SELECT my_sequence.NEXTVAL INTO varX FROM dual;
>
> Once you have the value, then compute the string you want out of it:
>
> key_val := 'S-'||LPAD(varX,8,'0');
>
> Then set your key column to this value;
>
> :key_colmn.new := key_val;
>
> Since you'll have three sequences you'll probably want some IF-THEN
> logic in your code if this trigger will handle all three sequences on
> the same table.
>
> HTH,
> Brian
>
>
> --
> ===================================================================
>
> Brian Peasland
> oracle_dba_at_nospam.peasland.net
> http://www.peasland.net
>
> Remove the "nospam." from the email address to email me.
>
>
> "I can give it to you cheap, quick, and good.
> Now pick two out of the three" - Unknown

Unfortunately he'll have more than three sequences, he'll have a sequence for each loan in this table, which could be hundreds or thousands, given his naming scheme for loan numbers:

loan_0001
loan_0002
loan_0003

loan_9997
loan_9998
loan_9999

And I expect this naming convention will need modification if more than 9999 loans have been generated. To have a sequence for each of these loans solely for the purpose of serialising the loan payments is, in my mind, ludicrous.

No matter how one looks upon this, it is a very poor idea.

David Fitzjarrell Received on Mon Apr 10 2006 - 14:56:08 CDT