Re: Creating table with a sequence as a Primary Key.....

oops...
just have seen: made a little mistake in the trigger - place the end if immediately before the end

(if the insert statement issued by the user already contains a value for the primary key, the code assumes that this value has been selected from the sequence, e.g.:

insert into xxxx (entry_number,....) values (seq_demo.nextval,.....)

if not you might run into oracle-error of duplicate value in the primary-key. To avoid that simply change the trigger to

.....
declare

    v_id number;
Begin

    select seq_demo.nextval

       into v_id
       from dual;

-> value for primary key is always specified by the trigger

regards

klaus

"Newbie (Joe )" <jranseth_at_hotmail.com> schrieb im Newsbeitrag news:wmO65.225$Hs3.4271_at_news1.mts.net...
> Thanks, Klaus!
>
> --
> "Klaus.Zeuch" <Klaus.Zeuch_at_t-online.de> wrote in message
> news:8jg78o$udd$10$1_at_news.t-online.com...
> > Hi,
> >
> > create your table:
> > create table xxxx (entry_number number(18),......) pctfree ...
 pctused...
> > tablespace .... storage (....);
> >
> > create primary key (if applying storage parameters you can place the
 index
> > on a different tablespace):
> > alter table xxxx add primary key (entry_number);
> >
> > create a sequence:
> > CREATE SEQUENCE seq_demo
> > INCREMENT BY 1
> > START WITH 1
> > MINVALUE 1
> > MAXVALUE 999999999999999999999999999
> > NOCYCLE
> > NOORDER
> > CACHE 20;
> >
> > create the trigger
> > CREATE OR REPLACE TRIGGER trg_demo
> > BEFORE INSERT
> > ON xxxx
> > REFERENCING NEW AS NEW OLD AS OLD
> > FOR EACH ROW
> > declare
> > v_id number;
> > Begin
> > if :new.entry_number is null then
> > select seq_demo.nextval
> > into v_id
> > from dual;
> > end if;
> > :new.entry_number := v_id;
> > End;
> >
> > hth
> >
> > Klaus
> >
> > "Newbie (Joe )" <jranseth_at_hotmail.com> schrieb im Newsbeitrag
> > news:GrL65.215$Hs3.3694_at_news1.mts.net...
> > > Hello again,
> > > I need to create a table that has a sequence in it as the primary
> > > key.....I have no idea how, as I am new to Oracle (8i), and databases
> > > altogether (VB programmer).
> > > I heard that I have to create a database trigger...is this
 correct?
 How
> > > do I do that?
> > > The table will be something like the following...
> > >
> > > ENTRY_NUMBER Sequence, PK
> > > STATE_CODE Varchar2, FK (references state table)
> > > PRODUCT_CODE Varchar2, FK (references product table)
> > > ENTRY_OWNER Varchar2
> > > DATE_ENTERED Date (format DD-MON-YY hh:mi:ss am)
> > > ......other entry details, etc.
> > >
> > > Could I please get some help in creating this table? Sample SQL or
 detailed
> > > answers would be greatly appreciated as I am not really a database
 guru.
> > > Thank you so much for anyhelp...
> > >
> > >
> > >
> > >
> >
> >
>
>
Received on Thu Jun 29 2000 - 00:00:00 CEST