Re: Autoincrement field

Thomas J. Kyte <tkyte_at_us.oracle.com> wrote:
> In article <8h0uu1$b64$17$1_at_news.t-online.com>,
> "Rainer Hagemann" <Rainer.Hagemann_at_t-online.de> wrote:
 

>> did someone know how i can create an
>> autoincrement field ( in informix it is an serial type) in
>> a oracle database table.
 

> we use sequences for that:
 

> create sequence myseq;
> create trigger my_trigger
> before insert on T for each row
> begin
> if ( :new.column_to_fill_in is null ) then
> select myseq.nextval into :new.column_to_fill_in from DUAL;
> end if;
> end;
> /

Just a remark about this technique, which I use in one of my databases. It should be noted that with this technique, subsequent inserts may fail if the columns filled by the trigger are primary keys or are otherwise constrained to be unique, i.e. if your first few inserts with the example above are like this:

    INSERT INTO T (another_column) VALUES ('This');
    INSERT INTO T (another_column) VALUES ('is');
    INSERT INTO T (column_to_fill_in, another_column) VALUES (4, 'just');
    INSERT INTO T (another_column) VALUES ('a');
    INSERT INTO T (another_column) VALUES ('test');

the value in column_to_fill_in will be 4 for both the "just" and the "test" rows.

Greetinx, Jan

-- 
 +- Jan T. Kim -------------------------------------------------------+
 |             email: kim_at_mpiz-koeln.mpg.de                           |
 |             WWW:   http://www.mpiz-koeln.mpg.de/~kim/              |
 *-----=<  hierarchical systems are for files, not for humans  >=-----*