Re: automatic value

From: Jonathan Gennick <gennick_at_worldnet.att.net>
Date: 3 Oct 1998 01:42:22 GMT
Message-ID: <361a7b20.4827882@netnews.worldnet.att.net>

On Fri, 02 Oct 1998 18:14:07 +0200, Cyril Matalon <mlvcxm04_at_nortel.ca> wrote:

>Is it possible to define a table with a column whose value would be
>increased automaticaly ?
>It's possible with MS ACCESS but with Oracle...

Yes. Look at the example that I've pasted in below.

Jonathan Gennick

SQL> create sequence key_for_table

  2         increment by 1
  3         start with 1
  4         nomaxvalue  --implies 10^27

  5 ;

Sequence created.

SQL>
SQL> select key_for_table.NextVal from dual;

  NEXTVAL

---

        1

SQL> select key_for_table.NextVal from dual;

  NEXTVAL

---

        2

SQL> select key_for_table.NextVal from dual;

  NEXTVAL

---

        3

SQL>
SQL> create table my_table (

  2          fld_pk  integer,
  3          fld_text        varchar2(2),
  4          constraint my_table_key
  5                  primary key (fld_pk)
  6          );

Table created.

SQL>
SQL> create or replace trigger my_table_set_key

  2          before insert on my_table
  3          referencing new as n
  4          for each row

  5 declare
  6 new_key integer;
  7 begin
  8 select key_for_table.nextval into new_key from dual;   9 :n.fld_pk := new_key;
 10 end;
 11 /

Trigger created.

SQL>
SQL> insert into my_table (fld_text) values ('A');

1 row created.

SQL> insert into my_table (fld_text) values ('B');

1 row created.

SQL>
SQL> select * from my_table;

   FLD_PK FL
--------- --

        4 A
        5 B

SQL>
SQL> insert into my_table (fld_pk,fld_text) values (1,'C');

1 row created.

SQL> select * from my_table;

   FLD_PK FL
--------- --

        4 A
        5 B
        6 C

SQL> commit;

Commit complete.

SQL> Received on Fri Oct 02 1998 - 20:42:22 CDT