Autonumber columns in Oracle
by
antonh
—
last modified
07-Sep-06 11:48 AM
In Access and SQL Server, you can set a field to be of type Autonumber. It then automatically increments the value in the field every time you add a new record. Oracle doesn't have this field type, but you can do it. Instructions follow.
In your Oracle db, set up a sequence (seq_id) for example:
CREATE SEQUENCE "SEQ_ID" INCREMENT BY 1 START WITH 1
MAXVALUE 1.0E28 MINVALUE 1 NOCYCLE
CACHE 20 NOORDER
Now, create a trigger that fires BEFORE an insert on your table (REQUEST_TABLE), and fills in the ID column from the sequence.NEXTVAL:
CREATE TRIGGER "NEW_REQUEST"
BEFORE INSERT ON "REQUEST_TABLE"
REFERENCING OLD AS OLD NEW AS NEW
FOR EACH ROW
BEGIN
SELECT SEQ_ID.NEXTVAL
INTO :NEW.ID
FROM DUAL;
END;
Forgot your password?
Clear and rebuild Plone's portal_catalog
