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

Andy Harkin 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

Hi Andy,

what you intend to do is:

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

But this has a major disadvantage:

If your transaction that inserts into tblcustomer takes a certain amount of time, and another user begins the transaction a few milliseconds after you started both records will get the same custumorid. This will leed to an error if you declared custumorid as primary key as I expect. The way Thomas Kyte pointed out is the wright one, since sequences always "produce" distinguished values.  

-- 
Regards

Matthias Gresz    :-)