Re: Problems with SEQUENCES!

From: Jonathan Gennick <gennick_at_worldnet.att.net>
Date: 6 Oct 1998 22:13:57 GMT
Message-ID: <361b7797.2365586@netnews.worldnet.att.net>

On Mon, 05 Oct 1998 18:21:37 +0200, "Lars Jütten" <juetten_at_ibh.rwth-aachen.de> wrote:

>I have a problem with SEQUENCES in databases. Although i think they are
>very usefull i don't know anything about their usage.
>How can i insert values into a table and use the defined sequence to
>generate a unique key value.

Lars, take a look at the following example.

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 Tue Oct 06 1998 - 17:13:57 CDT