You will often need to create auto-incrementing fields in your Oracle tables, most usually in order to generate a unique number for a table's primary key.
Although there is no inbuilt support for this in Oracle, it is a relatively simple process to make this behaviour happen. You need to use Oracle sequences to generate the numbers, and triggers to automagically give the ID field a value whenever you insert a row into the database.
We'll use the tried and tested emp table as an example, created with:
create table emp
(
id number(5),
name varchar2(20),
surname varchar2(30)
);
It's a bit simple, but we're only interested in one field in this case, the id field.
The next step is to create a sequence:
create sequence seq_emp_id
start with 1
increment by 1;
/
Next, the trigger:
create or replace trigger trig_emp
before insert on emp
for each row
begin
select seq_emp_id.nextval into :new.id from dual;
end;
/
Once created, this trigger will be activated whenever a row is inserted into the table.
The select statement may look daunting to everyone but Oracle gurus, but you should be able to understand the gist of what is going on. The next value is pulled out of the sequence and this value is entered into the id field.
and then issue a select statement, you will see that the id fields have been assigned a unique number without you having to explicitly ask it to in your PHP code:
SQL> select * from emp order by id;
ID NAME SURNAME
---------- -------------------- ------------------------------
1 Ali G
2 Keith Chegwin
