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

[mozart]

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

[marist89]

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.

[slimdave]

How about ...

Insert Into
   Emp
Select
   (Select Max(EMP#) from Emp) + rownum,
   name,
   surname
From
   Emp
/

... or maybe ...

Insert Into
   Emp
Select
   max_emp# + rownum,
   name,
   surname
From
   Emp,
   (Select Max(EMP#) max_emp# from Emp)
/