Re: ORACLE and a Counter field or Identity????

Andy,

Create a sequence.

Create a row level, before insert trigger that selects the nextval from the sequence and populates the "counter" field with the generated number.

Craig M. Wall

Andy Harkin wrote in message <3468633F.CE52DDAF_at_storm.ie>...
>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