Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: Clarification of 'Sequences'
On Wed, 30 Sep 1998 10:08:18 -0400, "steve hendrikse" <shend_at_sar-net.com> wrote:
>Good Day,
>
>I have reasonable experience with both PostgreSQL and Micrsoft SQLServer.
>WIth both I can create a column in a table that automatically generates a
>unique count value. That is:
>
>in MSSQL, i do:
> create tabel test1(
> id int identity(1,1) not null,
> value varchar(80)
> );
>
>in PostgreSQL, it is slightly more complex, but similar to:
>
> create sequence seq_id start 1 increment 1;
> create table test2(
> id int4 default nextval('seq_id') not null,
> value varchar(80)
> );
>
>now, in Oracle 8.0.4 (on Solaris), i believe that i must do something
>similar to the PostgreSQL case. I start with:
>
> create sequence seq_id start with 1 increment by 1;
>
>and then (?????)
>
> create table
>
> id int default seq_id.nextval not null,
> value varchar(80)
> );
>
>but this gets me an error saying that i cannot include a column name
>(seq_id) in the table definition.
>
>now, i conclude that if both MSSQL and PostgreSQL can do this (as shown
>above) there has to be a 'graceful' way to get Oracle to do it also.
>
Yup, there is.
You need to create a trigger on the table before insert.
eg.
SQL> create sequence seq_id start with 1 increment by 1;
Sequence created.
SQL> create table T(
2 id number,
3 value varchar2(80)
4 );
Table created.
SQL> create or replace trigger bi_t
2 before insert on T
3 for each row
4 begin
5 select seq_id.nextval
6 into :new.id 7 from dual;
Trigger created.
SQL> insert into t ( value ) values ( 'Test' );
1 row created.
SQL> insert into t ( value ) values ( 'Test Again' );
1 row created.
SQL> col value for a20
SQL> select * from T;
ID VALUE
---------- -------------------- 1 Test 2 Test Again
Hope this helps.
chris.
>please help!!!
>
>thanks in advance,
>steve
>
>
>
Received on Wed Sep 30 1998 - 00:00:00 CDT