Do you think there is any coincidence that "DUAL" is what you get when you try to type "F**K" in a text message on a Nokia? (Not that I've ever tried, of course...) David Landy, IT Consultant Business Intelligence Somerfield/KwikSave +44 (0) 117-301-8977 david.landy at somerfield.co.uk <mailto:david.landy at somerfield.co.uk> -----Original Message----- From: John.Brooking at sappi.com [mailto:John.Brooking at sappi.com] Sent: Tuesday, 10 February 2004 14:05 To: thelist at lists.evolt.org Subject: Re: [thelist] Oracle SQL >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. -- * * Please support the community that supports you. * * http://evolt.org/help_support_evolt/ For unsubscribe and other options, including the Tip Harvester and archives of thelist go to: http://lists.evolt.org Workers of the Web, evolt ! If you are not the intended recipient of this e-mail, please preserve the confidentiality of it and advise the sender immediately of any error in transmission. Any disclosure, copying, distribution or action taken, or omitted to be taken, by an unauthorised recipient in reliance upon the contents of this e-mail is prohibited. Somerfield cannot accept liability for any damage which you may sustain as a result of software viruses so please carry out your own virus checks before opening an attachment. In replying to this e-mail you are granting the right for that reply to be forwarded to any other individual within the business and also to be read by others. Any views expressed by an individual within this message do not necessarily reflect the views of Somerfield. Somerfield reserves the right to intercept, monitor and record communications for lawful business purposes.