2 Replies Latest reply on Jan 16, 2004 12:14 AM by marcoyeung

    Oracle trigger auto-key generation and JBOSS BEAN auto-key g

    marcoyeung Newbie

      Hi all,

      I have succefully configured my JBOSS 3.2.3 to use it entity-commands feature to automatic generate primary key for my table. I am useing Oracle 8.1.7.

      but what if my table has a trigger to generate primary key for SQL statment tooo (sql insertion), when BEAN get a sequence and then insert back the record to this table, it will fire the trigger in the table too. cause the primary key number advance 1 more, so in my table, I have primary key like : 1, 3, 5, 7 .........

      is there any one know how to solve this if I what to use JBOSS auto-key generation for BEAN and also trigger auto-key generation for normal SQL statement in one table.

      Pls HELP, my system can't lost primary key continuity.

      many thanks
      Marco

        • 1. Re: Oracle trigger auto-key generation and JBOSS BEAN auto-k
          Stjepan Brbot Newbie

          If your database is used only by your ent. application then you can disable or delete trigger generating primary key. If you have other legacy applications dealing with your database then it is not acceptable solution!

          Now, why your primary keys need this continuity? Having 1,3,5 for primary keys in not problem at all! Finally, first time when you delete a row from your table you will loose your "continuity"!

          If you have some special reason why you need this continuity try instead of "oracle-sequence" JBoss plugin another plugin "pk-sql" with

          SELECT sequencename.currval FROM DUAL

          for generating PK.

          • 2. Re: Oracle trigger auto-key generation and JBOSS BEAN auto-k
            marcoyeung Newbie

             

            "marcoyeung" wrote:
            thanks for your comment. actually, it is NOT that important to keep the primary key continuiyt, i just want them looks tidy at the beginning.

            Now I have solved the problem by modifing my trigger to not to generate primary key if the Bean already provided one.


            BTW, what is the difference between your 'pk-sql' and 'oracle-sequence'???


            thanks
            Marco