Re: how to use a sequence?

From: Jan Gelbrich <j_gelbrich_at_westfalen-blatt.de>
Date: Fri, 23 Apr 2004 13:38:44 +0200
Message-ID: <c6avfb$a4rpf$1@ID-93924.news.uni-berlin.de>

"Eric Lee" <lichoil_at_tom.com> schrieb im Newsbeitrag news:c6at0k$cak$1_at_mail.cn99.com...
> I have created a sequence named idx.
> How can I use this sequence when I create a table?
> I wanna create a table with primary key which can auto increment.
>
>
> thanks advance.
>

The common method is
writing a trigger like

CREATE or REPLACE TRIGGER myTable_ID_TR_BI BEFORE INSERT on myTable
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
BEGIN
IF :new.id IS NULL THEN

SELECT seq_myTable.NEXTVAL INTO :new.id FROM DUAL; END IF ;
END;
/

The sequence itself is completely independend of a table or any other schema object.
If You drop a table, all its dependent objects like synonyms, triggers a.s.o. dissappear - but not the sequence. The table is simply created by

CREATE TABLE xyz
(

ID NUMBER(10) PRIMARY KEY,
col1 ...
)

more information on
http://tahiti.oracle.com
search for TRIGGGER and SEQUENCE, You will find more examples.

hth, Jan Received on Fri Apr 23 2004 - 06:38:44 CDT