[thelist] Oracle SQL

[david.landy at somerfield.co.uk]

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.