Re: Auto Increment Primary key?

A copy of this was sent to "Peter Wilk" <PeterWilk_at_compuserve.com> (if that email address didn't require changing) On Sun, 2 Jan 2000 20:33:05 +0100, you wrote:

>hello,
> I want to simulate an AUTOINC field in Oracle with a
>sequence number using the following code - however
>I always get a compile-error for the trigger.
>can anybody help me with that ?
>thanks a lot - peter
>
>
>DROP TABLE SALEADMIN.TEST CASCADE CONSTRAINTS;
>
>CREATE TABLE SALEADMIN.TEST
> (
> TESTID NUMBER(8,0) NOT NULL,
> CONSTRAINT PK_SUBNUMBERROAD PRIMARY KEY(TESTID),
> TEXT VARCHAR2(25) NOT NULL
> )
> TABLESPACE SALEDATA;
>
>DROP SEQUENCE SALEADMIN.SEQ_TEST;
>CREATE SEQUENCE SALEADMIN.SEQ_TEST
> INCREMENT BY 1 START WITH 1
> MAXVALUE 99999999 MINVALUE 1
> CYCLE CACHE 20 ORDER;
>
>CREATE OR REPLACE TRIGGER SALEADMIN.TRIG_TESTID
> BEFORE INSERT ON SALEADMIN.TEST
> BEGIN
> SALEADMIN.TEST.TESTID = SALEADMIN.SEQ_TEST.NEXTVAL;
> END;
>

couple of things here:

1. if you use "show errors trigger <trigger_name>" the error will be shown...
2. = is used to test for equality in PL/SQL. := is assignment.
3. you cannot assign a SEQUENCE.NEXTVAL, you must select it from some table. Dual is typically used for this.
4. you cannot refer to the schema.table.column in the trigger. you must use the psuedo records :new and :old.
5. you need to use a BEFORE, FOR EACH ROW trigger to modify the rows values..

the trigger would be:

create or replace trigger trig_testid
before insert on test for each row
begin
  select seq_test.nextval into :new.testid from dual; end;
/

>rspeaker_at_my-deja.com schrieb in Nachricht <83o40t$qhe$1_at_nnrp1.deja.com>...
>>you can use the CREATE SEQUENCE command to create a sequence. Once
>>created, you can reference the pseudo-columns NEXTVAL and CURRVAL in
>>your select/insert statements. Then, make your sequence column the
>>PK for the table. For example, to insert a record using the next
>>available number you would do something like
>>INSERT INTO TABLE T VALUES (sequence.NEXTVAL, val2, val3, ...);
>>
>>Keep in mind that dropped values in the sequence do not automatically
>>get reassigned so you may have holes in your sequence. There are
>>additional parameters to the CREATE SEQUENCE command to handle things
>>such as the starting value, the increment value, the max value, and
>>whether or not to recycle the sequence when you reach the max value.
>>
>>HTH,
>>Roy
>>
>>
>>In article
>><Pine.LNX.4.10.9912171604300.29563-100000_at_alfred.laffeycomputer.com>,
>> John Shaft <shaft_at_meanmutha.com> wrote:
>>> Hi,
>>>
>>> Does Oracle has a way to define a table such that the primary key gets
>>> auto-incremented with each new insertion? If not, how do you keep your
>>> primary keys unique? (With a store value read and incremented in a
>>> transaction?)
>>>
>>> I have a databse devloped orignally for MySQL and want to port it to
>>> Oracle 8. I wondered about this issue.
>>>
>>> Thanks!
>>>
>>> shaft_at_meanmutha.com
>>> http://www.meanmutha.com
>>>
>>>
>>
>>
>>Sent via Deja.com http://www.deja.com/
>>Before you buy.
>

--
See http://osi.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Sun Jan 02 2000 - 13:59:00 CST