Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: ORACLE and a Counter field or Identity????
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_at_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_at_us.oracle.com
Oracle Government
Bethesda MD
http://govt.us.oracle.com/ -- downloadable utilities
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. Received on Tue Nov 11 1997 - 00:00:00 CST