Pulling NEXTVAL from Sequence using DB Trigger or DB Transaction

How to get ID attributes automatically filled with an increasing number value? Either on manual insert, per script insert or from java class.

For this issue you need the following:

1. Sequence
2. Trigger

-- Creating a Sequence
CREATE SEQUENCE  "MY_SEQ"  MINVALUE 1 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER  NOCYCLE;</p>

-- Creating a Trigger
CREATE OR REPLACE TRIGGER MY_TRIG
BEFORE INSERT
ON MY_TABLE
FOR EACH ROW
BEGIN
SELECT MY_SEQ.NEXTVAL INTO :NEW.MY_TABLE_ID FROM DUAL;
END;
/

-- enable / disable Trigger
ALTER TRIGGER "MY_TRIG" ENABLE;
ALTER TRIGGER "MY_TRIG" DISABLE;

Now with the trigger enabled you can simple insert data pulling an ID automatically from the sequence

by script:

Insert into MY_TABLE(MY_TABLE_ID,MY_ATTRIBUTE) values (REGIONS_SEQ.NEXTVAL,’Europe’);

or manually (i.e. with SQLDeveloper):

1) before commit:

Just leave the REGION_ID empty.

2) after commit:

The trigger has automatically pulled an increasing number value from the sequence for each ID attribute and row.

Get NEXTVAL using DB Transaction in Java class

You can get the NEXTVAL from a sequence by using an db transaction in your java class. (In that case the trigger on the database should be disabled otherwise your sequence increases twice. First call from the java class and second call from the db trigger.)

For example in JDev using ADF BC go to the entity object class, override the create method and put the following lines of code into it.

protected void create(AttributeList attributeList) {
super.create(attributeList);
// Sequence
SequenceImpl sequence = new SequenceImpl("MY_SEQ", getDBTransaction());
setAttribute("MyId", sequence.getSequenceNumber());
}

Now an automatic id is put into your id attribute using the sequence you defined in your database.

Try it out. See the attached sample:

Pulling NEXTVAL from Sequence using DB Trigger

Simply execute it in a DB Schema.

Requirements:
Oracle 10g, JDeveloper 10.1.3, SQLDeveloper 10.1.2