dcsimg
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: Incrementing a field(number) by 1 after an insert.

  1. #1
    Join Date
    Mar 2004
    Posts
    55

    Question Incrementing a field(number) by 1 after an insert.

    I have got a table say

    Employee
    --------
    EMP # NAME SURNAME
    1 John Smith
    2 Harry Toe
    3 ..... .....


    I need to insert some records from a second table which is exactly the same except I don't which to mess up my EMP #.

    I basically want the next EMp# to be (EMP# +1). In other words with each row inserted i want my number to increment by 1

    Now i understand this may involve some PL/SQL so I am looking for some guidance in terms of

    i)what is this called
    ii) any pointers to where I can get more info
    iii) if anyone might have any examples.

    I gather it will involve declaring some variable and looping.

    Thanks

  2. #2
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Why you want to do this, I could never guess. However, you are looking for an AFTER INSERT trigger. You can find all sorts of info and examples at http://tahiti.oracle.com.
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  3. #3
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    How about ...
    Code:
    Insert Into
       Emp
    Select
       (Select Max(EMP#) from Emp) + rownum,
       name,
       surname
    From
       Emp
    /
    ... or maybe ...
    Code:
    Insert Into
       Emp
    Select
       max_emp# + rownum,
       name,
       surname
    From
       Emp,
       (Select Max(EMP#) max_emp# from Emp)
    /
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

Posting Permissions

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


Click Here to Expand Forum to Full Width