Re: Help - Creating table with Sequence....

From: <prince_kumar_at_my-deja.com>
Date: 2000/06/30
Message-ID: <8jgo2h$5t1$1@nnrp1.deja.com>#1/1

Hi:

1. No, U do not need to refer it anywhere in the table definition
2. You may still need to define the primary key to avoid any accidental insertion of duplicate records.

ex:

CREATE TABLE <ur table name>

   (<ur single column> NUMBER CONSTRAINT <ur pk_const> PRIMARY KEY,

     col2   varchar2(10),
     col3   char(1)

   )

(or) if the primary key has more than one column,

CREATE TABLE <ur table name>

  ( col1               number,
    col2               varchar2(10),
    col3               char(1),
       CONSTRAINT <pk_const> PRIMARY KEY (col1, col2) )

3) They are just correlation names, you can just leave the

   REFERENCING clause.
  (they will be useful if you have table name called "NEW" or "OLD", so   that the "AS" clause can be changed to whatever value you want

  Ex: if u hav a table called "NEW", you can refer "NEW AS myNEW" and   use myNEW wherever :new is used)

  Yes, they mean the "old" value and "new" value.

Regards,
Prince.

In article <01M65.217$Hs3.4222_at_news1.mts.net>,   "Newbie \(Joe \)" <jranseth_at_hotmail.com> wrote:
> Do I have to reference this anywhere in my table definition? If not,
 how do
> I create a primary key...or do I have to?
> And what doe the OLD AS OLD NEW AS NEW mean? Is that old/new records?
> Thank you so much for your help!!
> <prince_kumar_at_my-deja.com> wrote in message
> news:8jg0t3$jlg$1_at_nnrp1.deja.com...
> > Hi:
> >
> > You can create a sequence like the following one,
> >
> > CREATE SEQUENCE <ur seq Name > INCREMENT BY 1 START WITH 1
> > NOMAXVALUE MINVALUE 1 NOCYCLE CACHE 20 NOORDER;
> >
> > Then Create a trigger like the one below.
> >
> > CREATE or replace TRIGGER <ur trigger Name >
> > BEFORE INSERT ON <ur table Name >
> > REFERENCING OLD AS OLD NEW AS NEW
> > FOR EACH ROW
> > Begin
> > select <ur seq>.nextval into :new.Entry_Number from dual ;
> > End ;
> >
> >
> > Regards,
> > Prince.
> >
> > In article <IYK65.186$Hs3.4104_at_news1.mts.net>,
> > "Newbie \(Joe \)" <jranseth_at_hotmail.com> wrote:
> > > 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...
> > >
> > >
> >
> >
> > Sent via Deja.com http://www.deja.com/
> > Before you buy.

>
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Fri Jun 30 2000 - 00:00:00 CDT