14 Replies Latest reply: Nov 30, 2012 9:58 AM by John Spencer RSS

    Automatic numbering on primary key (without sequence)

    923195
      Hello,

      i was wondering how to make automatic numbering of primary key without using sequence when "on" insert.

      At the moment i am using trigger:
      TRIGGER "scott"."do_numbers" BEFORE INSERT ON "scott"."test" 
      REFERENCING NEW AS NEW FOR EACH ROW 
      
      declare
       pragma autonomous_transaction;
      begin
      
      SELECT
       nvl(MAX(ID),0)+1
       INTO :NEW.ID
        FROM test;
      commit;
      END;
      the above trigger works when i am inserting row by row. But now i tried to insert 300 rows at once. Here insert fails ... i think there should be commit after each insert (thats what i think).

      Can someone explain it to me how can this "automatic" numbering can be done when inserting large number or rows from one table to another at once?

      thank you!
        • 1. Re: Automatic numbering on primary key (without sequence)
          Ora
          Why are you avoiding use of sequence?
          • 2. Re: Automatic numbering on primary key (without sequence)
            923195
            Hello,

            i know how to do this with sequence i am just asking how can it be done without using one.
            • 3. Re: Automatic numbering on primary key (without sequence)
              Keith Jamieson
              user13071990 wrote:
              Hello,

              i know how to do this with sequence i am just asking how can it be done without using one.
              Very Badly :)
              • 4. Re: Automatic numbering on primary key (without sequence)
                Chanchal Wankhade
                Hi,

                Without sequence you cannot do it... I have tried the same but didn't find any option..
                other database provide the option but not oracle yet.....
                • 5. Re: Automatic numbering on primary key (without sequence)
                  BluShadow
                  user13071990 wrote:
                  Hello,

                  i know how to do this with sequence i am just asking how can it be done without using one.
                  You wouldn't do it any other way than using a sequence, otherwise you will have issues in a multi-user environment.

                  The closest thing would be using an update statement in your trigger code...
                  UPDATE test
                  SET id = NVL(id,0)+1
                  RETURNING id INTO :new.id;
                  though this is not ideal and you should use sequences.

                  And you most certainly should not be using autonomous transactions inside a trigger. That's just wrong, especially when you're trying to manipulate the data on the triggering table.
                  • 6. Re: Automatic numbering on primary key (without sequence)
                    923195
                    Alright then sequence it is.

                    Thank you!
                    • 7. Re: Automatic numbering on primary key (without sequence)
                      BluShadow
                      And just as an example (though I still don't recommend you do this, and recommend you use sequences, as that's what they're for)...
                      SQL> create table test as select 0 as id from dual;
                      
                      Table created.
                      
                      SQL>
                      SQL> create table mysourcedata as select level as src_id from dual connect by rownum <= 10;
                      
                      Table created.
                      
                      SQL>
                      SQL> create table mytargetdata(id number, src_id number);
                      
                      Table created.
                      
                      SQL>
                      SQL> create trigger trg_mytarget before insert on mytargetdata
                        2  for each row
                        3  begin
                        4    update test
                        5    set    id = nvl(id,0)+1
                        6    returning id into :new.id;
                        7  end;
                        8  /
                      
                      Trigger created.
                      
                      SQL>
                      SQL> insert into mytargetdata (src_id)
                        2  select src_id from mysourcedata
                        3  /
                      
                      10 rows created.
                      
                      SQL>
                      SQL> select * from mytargetdata;
                      
                              ID     SRC_ID
                      ---------- ----------
                               1          1
                               2          2
                               3          3
                               4          4
                               5          5
                               6          6
                               7          7
                               8          8
                               9          9
                              10         10
                      
                      10 rows selected.
                      
                      SQL> select * from test;
                      
                              ID
                      ----------
                              10
                      
                      SQL>
                      • 8. Re: Automatic numbering on primary key (without sequence)
                        chris227
                        You cant even be sure, that your trigger fires only once

                        http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:4003705800346120218
                        • 9. Re: Automatic numbering on primary key (without sequence)
                          John Spencer
                          user13071990 wrote:
                          Hello,

                          i was wondering how to make automatic numbering of primary key without using sequence when "on" insert.
                          Upgrade to 12C as soon as it comes out :-) One of many new features is going to be an identity type column.

                          John
                          • 10. Re: Automatic numbering on primary key (without sequence)
                            BluShadow
                            John Spencer wrote:
                            user13071990 wrote:
                            Hello,

                            i was wondering how to make automatic numbering of primary key without using sequence when "on" insert.
                            Upgrade to 12C as soon as it comes out :-) One of many new features is going to be an identity type column.

                            John
                            New features eh.... maybe I'll wait for 12cR2 to allow for all the bug fixes on that one. ;)
                            • 11. Re: Automatic numbering on primary key (without sequence)
                              6363
                              John Spencer wrote:
                              user13071990 wrote:

                              i was wondering how to make automatic numbering of primary key without using sequence when "on" insert.
                              Upgrade to 12C as soon as it comes out :-) One of many new features is going to be an identity type column.
                              What are the chances that will lead to whole bunch of questions asking Where did all these sequences called something like SYSSEQ0000042 come from? I didn't create them ...
                              • 12. Re: Automatic numbering on primary key (without sequence)
                                Paul  Horth
                                3360 wrote:
                                John Spencer wrote:
                                user13071990 wrote:

                                i was wondering how to make automatic numbering of primary key without using sequence when "on" insert.
                                Upgrade to 12C as soon as it comes out :-) One of many new features is going to be an identity type column.
                                What are the chances that will lead to whole bunch of questions asking Where did all these sequences called something like SYSSEQ0000042 come from? I didn't create them ...
                                What are the chances people will still complain there are gaps in the sequence?
                                • 13. Re: Automatic numbering on primary key (without sequence)
                                  6363
                                  Paul  Horth wrote:
                                  3360 wrote:
                                  John Spencer wrote:
                                  user13071990 wrote:

                                  i was wondering how to make automatic numbering of primary key without using sequence when "on" insert.
                                  Upgrade to 12C as soon as it comes out :-) One of many new features is going to be an identity type column.
                                  What are the chances that will lead to whole bunch of questions asking Where did all these sequences called something like SYSSEQ0000042 come from? I didn't create them ...
                                  What are the chances people will still complain there are gaps in the sequence?
                                  About the same as the number of developers who never consider more than one user working with an application. So a lot.
                                  • 14. Re: Automatic numbering on primary key (without sequence)
                                    John Spencer
                                    3360 wrote:
                                    Paul  Horth wrote:
                                    3360 wrote:
                                    John Spencer wrote:
                                    user13071990 wrote:

                                    i was wondering how to make automatic numbering of primary key without using sequence when "on" insert.
                                    Upgrade to 12C as soon as it comes out :-) One of many new features is going to be an identity type column.
                                    What are the chances that will lead to whole bunch of questions asking Where did all these sequences called something like SYSSEQ0000042 come from? I didn't create them ...
                                    In the presentation I saw at Open World, it was pretty explicitly mentioned that this was aimed at SQL server types. since they would just use an identity column and never look at the data dictionary, maybe surprisingly few :-)
                                    >>
                                    What are the chances people will still complain there are gaps in the sequence?
                                    It seems to be an Oracle thing because people are aware of the sequences unlike an identity column where it is typically done as set and forget. People seem to assume that an identity is meaningless, at least in the little I hang around on SQL Server forumns, there do not seem to be the same number of questions around gap free sequences/identities.
                                    About the same as the number of developers who never consider more than one user working with an application. So a lot.
                                    That is a database agnostic position :-)

                                    John