Auto-increment in Oracle without using a trigger

oracle triggers auto-increment sequences

You can create and use oracle sequences. The syntax and details are athttp://www.techonthenet.com/oracle/sequences.php

Also read the articlehttp://rnyb2.blogspot.com/2006/02/potential-pitfall-with-oracle-sequence.htmlto understand the limitations with respect to AUTONUMBER in other RDBMS

oracle triggers auto-increment sequences

If you don't need sequential numbers but only a unique ID, you can use a DEFAULT of SYS_GUID(). Ie:

CREATE TABLE xxx ( ID RAW(16) DEFAULT SYS_GUID() )

oracle triggers auto-increment sequences

A trigger to obtain the next value from a sequence is the most common way to achieve an equivalent to AUTOINCREMENT:

create trigger mytable_trgbefore insert on mytablefor each rowwhen (new.id is null)begin    select myseq.nextval into :new.id from dual;end;

You don't need the trigger if you control the inserts - just use the sequence in the insert statement:

insert into mytable (id, data) values (myseq.nextval, 'x');

This could be hidden inside an API package, so that the caller doesn't need to reference the sequence:

mytable_pkg.insert_row (p_data => 'x');

But using the trigger is more "transparent".

CodeHunter