Re: Clarification of 'Sequences'

From: hlsousa <hlsousa_at_student.dei.uc.pt>
Date: 1998/10/03
Message-ID: <36156422.43676329@student.dei.uc.pt>#1/1

Steve,

in Oracle you can't define a column like an automate unique sequence.

The way to do this is creating a trigger that uses a sequence to fill the column.
Try this :

    create table mytable

        id        int    not null,
        value   varchar(80)
        constraint    pk_id    primary key (id)

    );

    create sequence mytable_seqid start with 1 increment by 1;

    create or replace trigger mytable

        before insert on mytable
        for each row
    declare
        newid mytable.id%type;
    begin
        select mytable_seqid.nextval into mewid from dual;
        :new.id:=newid;

    end;
/

Now every time you insert values in your table the trigger will substitute the value in the id field, for the newid.

try this lines:

    insert into mytable values(0, 'line one');
    insert into mytable values(0, 'line two');
    insert into mytable values(0, 'line three');

Good Work,

Helder Sousa Received on Sat Oct 03 1998 - 00:00:00 CDT