"Identity" in Oracle

[jgotthelf]

Hello,<br><br>I'm just starting to work with Oracle, so forgive me if I'm asking too basic a thing here.<br><br>I am trying to figure the best way to populate the primary key field of tables.&nbsp;&nbsp;Using SQL Server I set the field up as an &quot;Identity&quot; field, which caused the server to automatically increment a value and put it into that field on each insert.&nbsp;&nbsp;What is the best way to do this in Oracle 8i?<br><br>Joe G.

 

[tonedef]

You can create a <b>sequence</b> to generate the incrementing number.&nbsp;&nbsp;Then you need to create a <b>trigger</b> that executes <b>on insert for each record</b> on the table you need the incrementing value and do <br><FONT FACE=monospace><br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;SELECT<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<i>sequence</i>.nextval<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;INTO<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;:new.<i>field</i><br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;FROM<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;dual;

 

[jgotthelf]

Thanks!

 

[jgotthelf]

Eggselent - the sequence works fine, but I'm not sure how to build the trigger - I have:<br><br>create trigger test_addid before insert on mytest<br>for each row<br>begin<br>&nbsp;&nbsp;:new.fred_id := select get_my_id.NextVal from dual; <br>end;<br><br>I also tried:<br>:new.fred_id := get_my_id.NextVal<br><br>and some other variations.&nbsp;&nbsp;They all produce the surprisingly useless message:<br><br>[Oracle][ODBC][Ora]Trigger, procedure or function created with PL/SQL compilation error(s).<br><br>Any ideas?<br><br>Thanks again - I'll learn quickly, please be patient with me!<br><br>Joe G.

 

[tonedef]

Joe,<br><br>You must use the <b>SELECT...INTO</b> statement that I put in my original post not the assignment statement that you are trying.<br><br>Tony