From: "Craig M. Wall" <cwall@petersons.com>
Subject: Re: ORACLE and a Counter field or Identity????
Date: 1997/11/11
Message-ID: <64a7qh$5gs@news9.noc.netcom.net>#1/1
References: <3468633F.CE52DDAF@storm.ie>
X-MimeOLE: Produced By Microsoft MimeOLE V4.71.1712.3
Organization: Netcom
X-NETCOM-Date: Tue Nov 11 12:23:13 PM CST 1997
Newsgroups: comp.databases.oracle.misc



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@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
>