[thelist] Oracle SQL

[John.Brooking at sappi.com]

>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.