If we do not already have a table to work with, we will create a sample table for this purpose.
create table schema.test_table ( row_id number(5), name varchar2(50), status varchar2(1), entry_date date );
Let us say we want to make the “row_id” field an “auto-increment” field. To do so, we need a sequence object first. The code below gives us an sequence that starts off at 1 and capped at 99999.
create sequence schema.seq_test_table_row_id minvalue 1 maxvalue 99999 start with 1 increment by 1 nocache;
All we have left is the actual “auto-increment” part. To do this, we build a very simple trigger on the table.
create trigger schema.trg_test_table_row_id before insert on schema.test_table for each row begin if (:NEW.row_id) is null then select schema.seq_test_table_row_id.nextval into :NEW.row_id from dual; end if; end;
Now, when you insert into the table schema.test_table, if you wish to use the sequence object as an auto-incrementer, just do not enter the row_id field. Two examples of using this auto-incrementer below:
insert into schema.test_table (name, status, entry_date) values('John Doe','1',sysdate); insert into schema.test_table values(null,'John Doe','1',sysdate);
What if we want to override this trigger and use a number of our choice for the row_id field? No problem, just insert the value as you would normally. Note the trigger has an if-clause in it; the trigger will only have an effect (ie. auto-increment from the sequence) if the incoming row_id field is not populated.