12 Replies Latest reply: Apr 2, 2013 11:16 AM by chris227 RSS

    sequance nextval

    997232
      Hi i got sequance
      CREATE SEQUENCE "BAZA"."SEKW_ID" MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 10001 CACHE 10000 NOORDER NOCYCLE ;

      and trigger

      create or replace
      trigger doda_id
      BEFORE INSERT ON spolki
      FOR EACH ROW
      BEGIN
      :new.spolka_id :=sekw_id.nextval;
      END;


      when i delete the row i created it gives me new value like i created 1,2 and deleted 1 then run trigger and i have 2,3 how to do it to have 1,2 after running trigger?
        • 1. Re: sequance nextval
          SomeoneElse
          how to do it to have 1,2 after running trigger?
          Nope. Triggers don't work that way. They are just generators with no intelligence.

          Edited by: SomeoneElse on Apr 2, 2013 7:53 AM

          (changed the word counters to generators)
          • 2. Re: sequance nextval
            997232
            so what should i do to fix this ??
            • 3. Re: sequance nextval
              jeneesh
              994229 wrote:
              so what should i do to fix this ??
              Dont fix it..

              It should be 2 a,nd 3 only..

              Why do you want to make it 1 and 2 ? Any business rule? What about the records in child tables, when you change the ID? What you want to happen in a multi user environment?

              And it will lead to serialization..

              See all these discussions:

              sequence numbers - with no gaps!
              http://asktom.oracle.com/pls/apex/f?p=100:11:0::NO::P11_QUESTION_ID:1133611000346070688
              • 4. Re: sequance nextval
                SomeoneElse
                so what should i do to fix this ??
                Why do you need this behavior?

                What business purpose does it solve?
                • 5. Re: sequance nextval
                  997232
                  i want to make continous numbering after deleting row
                  • 6. Re: sequance nextval
                    sybrand_b
                    Why? Do these numbers have meaning to you?

                    --------------
                    Sybrand Bakker
                    Senior Oracle DBA
                    • 7. Re: sequance nextval
                      Paul  Horth
                      994229 wrote:
                      i want to make continous numbering after deleting row
                      No, that would not scale or work well in a multi-user environment.

                      Anyway, you were asked what the business reason was. Why does the business think they need continuous numbering on the rows?
                      • 8. Re: sequance nextval
                        Peter Gjelstrup
                        994229 wrote:
                        i want to make continous numbering after deleting row
                        Meaning in a table with 1.000.000.0000.001 rows, deleting one would mean potentially updating 1.000.000.0000.000 ?

                        Best of luck, you are going down the worst possible track.
                        Peter
                        • 9. Re: sequance nextval
                          997232
                          just my idea no business reasons but you right good example with 10000000000000000000000 rows :)
                          • 10. Re: sequance nextval
                            EdStevens
                            994229 wrote:
                            just my idea no business reasons but you right good example with 10000000000000000000000 rows :)
                            Plus, with your cache of 10000, you stand to loose forever those 10000 numbers for any number of reasons, before they are even used.
                            • 11. Re: sequance nextval
                              rp0428
                              >
                              i want to make continous numbering after deleting row
                              >
                              Tom Kyte, Oracle ACE and noted author, said it best over 12 years ago:

                              SEQUENCES WILL NEVER GENERATE A GAP FREE SEQUENCE OF NUMBERS!

                              See this AskTom is from 2001. It is just as valid now as it was then
                              http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:530735152441
                              >
                              Sequences will never generate a gap free sequence of numbers.

                              If someone calls seq.nextval and doesn't insert the value somewhere (or does insert it
                              and later rolls back their transaction) that number is GONE.

                              Sequences are database objects and may be aged out of the shared pool like everything
                              else -- it doesn't take a database shutdown to produce a gap.

                              You should never count on a sequence generating anything even close to a gap free
                              sequence of numbers.
                              • 12. Re: sequance nextval
                                chris227
                                It seems that you want that ordering by time of insert.
                                You may consider to save the time of insertion in a separate column, creating a view on it with a an column with the rwo number like
                                row_number() over (order by insert_date)