Re: Guaranteed Order With a Sequence / Trigger

No!!! Merely by issuing the statement SELECT myTableSeq1.nextval FROM dual will you increment the sequence, and will cause a gap. There's no way to prevent someone from doing this except maybe through grants, etc.

Are the numbers absolute relative to the rows i.e. would rownum suffice upon a select?

<john_galt29_at_my-deja.com> wrote in message news:7utshv$jl7$1_at_nnrp1.deja.com...
> I would like to use a certain column in my table to perform some
> calculations and as such I need to be sure of the numbers that are in
> that column. Specifically I want that column to start with the value 1
> and go up from there, incremented by 1 every time a record is added. I
> am using the following sequence / trigger combo:
>
> CREATE SEQUENCE myTableSeq1 INCREMENT BY 1 START WITH 1 ORDER NOCACHE;
>
> CREATE OR REPLACE TRIGGER myTableT1 BEFORE INSERT ON myTable
> FOR EACH ROW
> BEGIN
> SELECT myTableSeq1.nextval INTO :new.id FROM dual;
> END;
>
> Does this ABSOLUTELY GUARANTEE that the sequence will NEVER be out of
> order, that there will never be a gap in the numbers? Or should I do
> something at the application level, like lock the table before doing an
> insert? I would prefer *NOT* to do that ...
>
> Thanks for any help or comments!
> John
>
>
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
Received on Sun Oct 24 1999 - 10:42:40 CDT