Autoincrement in Oracle 8.1.7

From: Norman Dunbar <Norman.Dunbar_at_lfs.co.uk>
Date: Mon, 29 Apr 2002 13:21:17 +0100
Message-ID: <E2F6A70FE45242488C865C3BC1245DA702088C1F@lnewton.leeds.lfs.co.uk>

Dave,

Oracle doesn't allow this to happen automatically like some other databases, however, you can do it as follows :

create a sequence which generates the required id number create a trigger on the table which fills in the id number from the sequence if there is no id number in there already.

Tis done !

Example :

CREATE SEQUENCE seq_test
START WITH 1
INCREMENT BY 1
MINVALUE 1
MAXVALUE 9999
NOCYCLE
CACHE 5 Sequence created

CREATE TABLE TEST(ID NUMBER(4) NOT NULL, STUFF VARCHAR2(30)) Table created

ALTER TABLE TEST ADD CONSTRAINT PK_TEST PRIMARY KEY (ID) Table altered

CREATE OR REPLACE TRIGGER TRG_TEST_BI
 BEFORE INSERT ON TEST
FOR EACH ROW
BEGIN
   SELECT seq_test.NEXTVAL INTO :NEW.id FROM dual; END trg_location_bi;

Trigger created

INSERT INTO test (stuff) VALUES ('some stuff') 1 row inserted

INSERT INTO test (stuff) VALUES ('some more stuff') 1 row inserted

INSERT INTO test (stuff) VALUES ('a bit of stuff') 1 row inserted

COMMIT
Commit complete

SELECT * FROM test

   ID STUFF

----- ------------------------------
    1 some stuff                    
    2 some more stuff               
    3 a bit of stuff                

3 rows selected

HTH Regards,
Norman.

---

Norman Dunbar
Database/Unix administrator
Lynx Financial Systems Ltd.
mailto:Norman.Dunbar_at_LFS.co.uk

Tel: 0113 289 6265
Fax: 0113 289 3146
URL: http://www.Lynx-FS.com


-------------------------------------

Received on Mon Apr 29 2002 - 07:21:17 CDT