Oracle auto_increment

Anon

This may not be the right place to ask, but I figure someone out there has to know.

How do I accomplish a MySQL-like auto_increment in Oracle8i? I've tried using sequences, but you have to reference them everytime. I want something that I don't have to ask to increment, it just does. (And not by just using ROWID - I need a different approach).

Thanks~

Brett

Anon

Create a sequence then create a before insert trigger for each row for the table u want auto increment and in that trigger say :new.<column name> := sequence value;

something like,
CREATE OR REPLACE TRIGGER trg_test
BEFORE INSERT ON test

FOR EACH ROW
DECLARE ln_num NUMBER;
BEGIN
SELECT test.nextval into ln_num FROM dual;
:new.col:=ln_num;
END;

Anon

Thanks! Seems to do the trick!

Brett

Anon

YOU WILL HAVE PROBLEMS IN A REAL ENVIROMENT WITH THIS TRIGER.

Anon

I havne't actually played with it yet, too busy. Why will it cause problems?

Anon

NOT REALLY BIG PROBLEM BUT,

WHEN YOU DESIGN A TRIGER IN ORDER TO HANDLE A SEQUENCE YOU SHOULD HAVE IN MIND THAT ALL THE APPLICATIONS WILL USE THE RETURNING VALUE FROM THIS TRIGER (IN ORDER TO AVOID THE GAPS).

CONSIDER THIS:
YOU NEED TO INSERT A NEW LINE FROM SQL.PLUS FOR EXAMPLE:

-YOU ALREADY HAVE A SEQUENCE FOR THIS TABLE :
"emp_seq.nextval".
(assume the triger uses the same sequence)

IF YOU DO NOT use the above triger THIS LINE SOULD BE OK:

insert into emp(empno,ename) values(emp_seq.nextval,'name');

BUT THE SAME TIME THE VAULE FROM THE TRIGER INSTREASED. SO NEXT TIME YOU WILL USE YOUR PHP PAGE USING THE TRIGER YOU WILL HAVE A
emp_seq.nextval+2

IN ORDER TO AVOID THIS YOU SHOULD CREATE AN ANONYMOUS BLOCK IN THE SQL/PLUS TO OBTAIN THE VALUE FROM THE TRIGER.

Brett Stimmerman wrote:

I havne't actually played with it yet, too busy. Why will it cause problems?

Anon

SORRY, FORGOT TO MENTION:

JUST USE THE STATEMENT:

Insert into emp(empno,ename) values(emp_seq.nextval,'name');

I SUPPOSE IS FINE INTO YOUR PHP PAGE!! (YOU SHOULD HAVE CREATE A SEQUENCE FOR YOUR TABLE)

TRY IT AND TELL ME!