ORACLE and a Counter field or Identity????

Problem:

I've have an application in MSAccess in which i use counters fields on the tables and worked fine
with MSSQL 6.5 but now I'm trying to get this application to use ORACLE7 on WinNT4.0 SP3

I don't know how to setup a table with a counter or a Identity field, so I gave up on that side.
Then i thought leave it as a number then create a trigger.

Here is my attempt:

create trigger cust_trig before insert on tblcustomer for each row begin
update tblcustomer set new.customerid = (select max(customerid)+1 from tblcustomer) ;
end;

This statement processed fine but as so as i tried a insert on this table i got these errors

SQLWKS> INSERT INTO TBLCUSTOMER (CUSTOMERNAME) VALUES ('ANDY');

ORA-00904: invalid column name
ORA-06512: at "THERMO.CUST_TRIG", line 2
ORA-04088: error during execution of trigger 'THERMO.CUST_TRIG'

Has anybody out there a neat way of having a counter field on a table in ORACLE7??? Help is greatly needed here.

Cheers for all your help in advance.

Andy Received on Tue Nov 11 1997 - 00:00:00 CST