>Date: Tue, 10 Feb 2004 13:50:45 +1100 >From: Mark M <mark at safefrombees.com> > ... >A good tute can be found here: http://www.llcsystems.com/FAQ/oracle_SQL/oracle_SQL_14.htm I would add that to get truly automatic numbering, people often put a "trigger" on the table where the sequence is used. A full explanation of triggers is beyond the scope of this message, but more information can be found in many places on- and off-line. The basic idea in this situation is that you define a trigger such that every time a row is inserted by any means whatsoever (programmatically *or* through a query tool such as SQL*Plus), a bit of SQL is run to insert the sequence's nextval into the field. In terms of that tutorial's example, you could define a trigger on the "students" table thusly: CREATE OR REPLACE TRIGGER trg_student_id -- Give trigger a name BEFORE INSERT -- The type of trigger ON students -- The table it's on REFERENCING NEW AS NEW OLD AS OLD -- H2 prefix prior/new column values FOR EACH ROW BEGIN -- Here is the SQL to run: SELECT student_id.NEXTVAL -- Get sequence's NEXTVAL INTO :NEW.student_id -- Put it into the student_id column FROM DUAL; -- "DUAL" is Oracle's fake table name END; When this is working, you should not include the "student_id" column in any of your INSERT statements, as the trigger is now handling that for you. If you do, both you and the trigger will assign the number (the trigger last, I think, but don't quote me on that), and you will find that your sequence values are being used up twice as fast! Also, the tutorial doesn't mention this, but if you are using the "student_id" column as the primary key, go ahead and declare it as such, so that it will not allow nulls or duplicates, and will be indexed. - John P.S. I've always wondered this, and no one has ever been able to give me an answer. Why the heck is "DUAL" used as the pseudo-table name? What is that supposed to mean? Anyone here know? This message may contain information which is private, privileged or confidential and is intended solely for the use of the individual or entity named in the message. If you are not the intended recipient of this message, please notify the sender thereof and destroy / delete the message. Neither the sender nor Sappi Limited (including its subsidiaries and associated companies) shall incur any liability resulting directly or indirectly from accessing any of the attached files which may contain a virus or the like.