From: tkyte@us.oracle.com (Thomas Kyte)
Subject: Re: ORACLE and a Counter field or Identity????
Date: 1997/11/11
Message-ID: <346d7b5c.91183384@newshost>#1/1
References: <3468633F.CE52DDAF@storm.ie>
Organization: Oracle Government
Reply-To: tkyte@us.oracle.com
Newsgroups: comp.databases.oracle.misc




Use a sequence.  In your case, try:

create sequence customerid_seq;

create trigger tblcustomer_bifer
before insert on tblcustomer
for each row
begin
   select customerid_seq.nextval into :new.customerid from dual;
end;
/


On Tue, 11 Nov 1997 13:53:03 +0000, Andy Harkin <aharkin@storm.ie> wrote:

>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

 
Thomas Kyte
tkyte@us.oracle.com
Oracle Government
Bethesda MD
 
http://govt.us.oracle.com/    -- downloadable utilities
 
----------------------------------------------------------------------------
Opinions are mine and do not necessarily reflect those of Oracle Corporation
 
Anti-Anti Spam Msg: if you want an answer emailed to you, 
you have to make it easy to get email to you.  Any bounced
email will be treated the same way i treat SPAM-- I delete it.