Re: Creating Sequence for Auto Number

In article our last gripping episode jkipp_at_mbna.com wrote:
> I am trying to create a sequence to auto number a column in a table.
> There is a small section on it in the ORacle book I am reading but it
> is not detailed enough for me to try it.
> I know I have to use the 'create sequence' command and something
 called
> the NextVal thingie.
> Would I have to create some sort of trigger(something else I don't
 know
> how to do) to tell the data to increment on say an insert, update?
>
> Thanks for any help
>
> Jim
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
>

If you are not coding an application to perform such duties then, yes, a trigger will be necessary to populate your 'auto-number' column. And this can be tricky, as you may well receive 'mutating table' errors. To alleviate this you should visit
http://govt.us.oracle.com/~tkyte/Mutate/index.html where Tom Kyte discusses this and provides excellent solutions.

If you are coding an application to insert/update/delete/select data from your table then a database trigger will not be necessary. Code your application to utilize the sequence to populate the 'auto-number' column:

...

     select auto_seq.nextval
     into new_seq
     from dual;
...
     insert into ....
     values
     (new_seq, :blk1.value, :blk1.value2, ...)

Of course, this example is for Oracle Forms (thus the :blk1 descriptors), but any application would be coded to perform similar operations.

--
David Fitzjarrell
Oracle Certified DBA


Sent via Deja.com http://www.deja.com/
Before you buy.