Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Feb 2009
    Posts
    36

    Question Unanswered: Auto-increment Of Primary Key

    HIII
    CAN ANYONE TELL ME-HOW TO AUTOMATICALLY INCREMENT THE PRIMARY KEY VALUE WHENEVR I WANT TO INSERT A NEW RECORD....
    MY PRIMARY KEY SHOULD START WITH 'TR' FOLLOWED BY ANY INTEGER.
    FOR EXAPLE:
    TRO1
    TRO2
    TR03
    TR04 AND SO ON.........................................

  2. #2
    Join Date
    Feb 2009
    Posts
    62
    Create a Before Insert trigger on the table that fires for each row.
    Have that trigger get the next value from a sequence, convert that value into a string with the required number of leading zeroes, prepend the letters 'TR' to that string, and populate your primary key field with this composite value.

  3. #3
    Join Date
    Dec 2008
    Posts
    135
    create table ers(id int , empname varchar2(32))
    create sequence seq_1 start with 1 increment by 1

    ---while inserting data
    insert into ers values(1,'TR'||seq_1.nextval)
    select * from ers

  4. #4
    Join Date
    Feb 2009
    Posts
    36

    auto-increment of primary key

    hiii
    thanks alot for code for primary key generation...i wil try it out definitely.

  5. #5
    Join Date
    Feb 2009
    Posts
    36
    hii bklr
    your code is working fine on pl/sql but can u tell me how to inplement it using developer 2000 (in forms designing).

  6. #6
    Join Date
    Dec 2008
    Posts
    135
    Quote Originally Posted by ANGEL_G
    hii bklr
    your code is working fine on pl/sql but can u tell me how to inplement it using developer 2000 (in forms designing).
    do u want the code in sql developer 2000
    then u will use this code in insert sp
    DECLARE @refNo VARCHAR(50),
    @referencenumber VARCHAR(32)

    SELECT @ReferenceNumber = ''

    SELECT @ReferenceNumber = MAX(empid)
    FROM emptable

    PRINT @ReferenceNumber

    IF ISNULL(@ReferenceNumber,'') = ''
    SELECT @refno = 'TR-'+ '01'
    ELSE
    SELECT @refno = 'TR-'+ RIGHT('00000' + CAST(CAST(RIGHT(@ReferenceNumber, 4) AS INT) + 1 AS VARCHAR(6)), 6)

    SELECT @referencenumber = @refno
    SELECT @referencenumber

  7. #7
    Join Date
    Feb 2009
    Posts
    36
    Quote Originally Posted by bklr
    do u want the code in sql developer 2000
    then u will use this code in insert sp
    DECLARE @refNo VARCHAR(50),
    @referencenumber VARCHAR(32)

    SELECT @ReferenceNumber = ''

    SELECT @ReferenceNumber = MAX(empid)
    FROM emptable

    PRINT @ReferenceNumber

    IF ISNULL(@ReferenceNumber,'') = ''
    SELECT @refno = 'TR-'+ '01'
    ELSE
    SELECT @refno = 'TR-'+ RIGHT('00000' + CAST(CAST(RIGHT(@ReferenceNumber, 4) AS INT) + 1 AS VARCHAR(6)), 6)

    SELECT @referencenumber = @refno
    SELECT @referencenumber











    hii bklr
    thanks alot for help but as i m new to pl/sql,i m not able to understand the following line of code
    SELECT @refno = 'TR-'+ RIGHT('00000' + CAST(CAST(RIGHT(@ReferenceNumber, 4) AS INT) + 1 AS VARCHAR(6)), 6)

    if you dnt mind can u explain it little bit.should i send you the whole detail of my form in developer 2000;

  8. #8
    Join Date
    Jan 2009
    Posts
    17
    Quote Originally Posted by ANGEL_G
    hii bklr
    thanks alot for help but as i m new to pl/sql,i m not able to understand the following line of code
    SELECT @refno = 'TR-'+ RIGHT('00000' + CAST(CAST(RIGHT(@ReferenceNumber, 4) AS INT) + 1 AS VARCHAR(6)), 6)

    if you dnt mind can u explain it little bit.should i send you the whole detail of my form in developer 2000;
    It is Sql server code not oracle code because there is no Right fn in oracle
    developer 2000 and select syntax also not valid in oracle

  9. #9
    Join Date
    Feb 2009
    Posts
    36
    Quote Originally Posted by ANGEL_G
    hii bklr
    thanks alot for help but as i m new to pl/sql,i m not able to understand the following line of code
    SELECT @refno = 'TR-'+ RIGHT('00000' + CAST(CAST(RIGHT(@ReferenceNumber, 4) AS INT) + 1 AS VARCHAR(6)), 6)

    if you dnt mind can u explain it little bit.should i send you the whole detail of my form in developer 2000;
    plz tell me the code for developer 2000 if possible

  10. #10
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Use a on insert trigger on the block and write out your insert code in the trigger. See the example in the help form for "ON-INSERT" and your insert would be something like

    insert into my_table(col1,col2,col3)
    values('TR'||lpad(my_table_seq.nextval,10,'0'),:my _block.col2,:my_block.col3);
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  11. #11
    Join Date
    Feb 2009
    Posts
    36
    hii beilstwh
    your code is giving no error...but whenever i try to insert data into the table through form i have to enter the id(TR...) but i want this to be auto increment it whenever i need to insert a ner record into database...

  12. #12
    Join Date
    Feb 2009
    Posts
    36
    HII AGAIN beilstwh
    YOUR CODE IS WORKING PERFECT.THANKS ALOT
    BUT ONE PROBLEM IS STILL THERE-THE INCREMENTED VALUE OF ID IS NOT GETTING DISPLAYED IN THE FORM..IT IS INCREMENTED IN ORACLE BACK END ONLY...SO HOW TO DISPLAY THE VALUE IN THE FORM ALSO???

  13. #13
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by ANGEL_G
    HII AGAIN beilstwh
    YOUR CODE IS WORKING PERFECT.THANKS ALOT
    your caps lock key is b0rken
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  14. #14
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    set the default value of the field on the form to

    'TR'||SEQUENCE.my_table_seq.NEXTVAL
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  15. #15
    Join Date
    Feb 2009
    Posts
    36
    Quote Originally Posted by beilstwh
    set the default value of the field on the form to

    'TR'||SEQUENCE.my_table_seq.NEXTVAL


    hi
    when i m writing the code given by you in item's initial value property in object navigator,it is giving me the error as follow:

    frm:11324 value is invalid...
    what should i do now??

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •