Re: Guaranteed Order With a Sequence / Trigger

John,

As others have said, using a sequence is not going to guarantee an ordered list of numbers without gaps.

There is a DBMS_UTILITY.GET_TIME function. (I am a little fuzzy on the exact name, it might be something else close to that). This function will return the system time to 100ths of a second. You might try to have your application use it to insert the exact time of insert into the table. Suppose you call the column date_created.

Then, write a query like this:

UPDATE my_table mt1

SET           order_column = (
                        SELECT   count(*)
                        FROM       my_table mt2
                        WHERE    ( mt2.date_created < mt1.date_created OR
                                             (mt2.date_created =
mt1.date_created AND
                                               mt2.id <= mt1.id) ) )

That will update the order_column with an unbroken list of numbers that are in order according to date_created. This update could run in a statement_level POST_INSERT trigger on my_table if you want, but if my_table is big, the performance might suffer. There are ways around that though. Unfortunately, a row level trigger cannot work because my_table will be mutating at that point.

Off-hand, I can't think of another way without locking the table.

Hope this helps,

Matt

john_galt29_at_my-deja.com wrote in message <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 - 09:54:13 CDT