Re: Automatic primary key in Oracle

From: Jared Still <jared_at_valleynet.com>
Date: 1996/01/23
Message-ID: <4e376l$3e3_at_alpine.valleynet.com>#1/1

Veikko V{{t{j{ TKKK (vvaataja_at_news.abo.fi) wrote:
>: How to use the oracle sequence feature? I want to use it for an auto
>: increment primary key field but I can't figure out how to do it. If I'm
>: doing something totally wrong, tell me! This is what I do:
 

>: First I create the sequence with CREATE SEQUENCE SEQ_PRKEY;. Then I
>: create a table that uses that key with CREATE TABLE SOMETABLE (ID INTEGER
>: NOT NULL, SOMEFIELD VARCHAR(10),...,PRIMARY KEY(ID)); Oracle manuals say
>: clearly that I can't use sequences in default values, so I figure that I need
>: a trigger to put the sequence number in a record records primary key field.
>: But how do I get the sequence number in a PL/SQL trigger??? Oracle doesn't
>: accept NEW.ID:=SEQ_PRKEY.NEXTVAL; in the trigger body. WHY?
 

>: Only thing that I want to do is to have an totally automatic primary key
>: field. In interbase it works just like I describe above. How do I do it in
>: Oracle???

Here are 2 scripts. the first will create some test objects. The second will insert data into the test tables.

The PK will be generated by a trigger.

• CUT HERE ---

rem test triggers for primary key inserts

drop table emp cascade constraints;
drop table emp2 cascade constraints;
drop sequence emp_seq;

create table emp

	(	id number(12,0) not null , 
		lname varchar2(20),
		fname varchar2(20),
		constraint jemp_pk primary key (id)  
			using index pctfree 5 tablespace jared 
	)

/

create table emp2

	(	id number(12,0) not null , 
		status varchar2(20)
	)

/

create index emp_2idx on emp2(id);

create sequence emp_seq

        minvalue 1000 start with 1000 order
/

REM this example shows how to create a trigger to create a primary REM key for a table
REM
REM cannot use sequence directly; must store to a variable REM and then assign to column

CREATE or replace trigger emp_pk_trigger

	before insert on emp
	for each row		-- row level trigger needed to use ":new"
DECLARE
	NewID integer;
	KeyExists exception; -- don't allow user to insert PK

BEGIN

	if :new.id IS NOT NULL then
	 	raise KeyExists;
	end if;

	select emp_seq.nextval into NewId from dual;
	:new.id := NewId ;
	dbms_output.enable;
	dbms_output.put_line(to_char(:new.id));

EXCEPTION
	WHEN KeyExists THEN
		raise_application_error(-20000,'Primary Keys are generated for EMP

table');
END;
/

CREATE or replace trigger emp_pk_trigger2

	after update on emp
	for each row		-- row level trigger needed to use ":new"
DECLARE 
	NewID varchar2(255);
	status number;
BEGIN
	dbms_output.get_line(NewID,status);
	insert into emp2(id,status)
	values (to_number(NewID), 'this the status col');

END;
/

• CUT HERE -----------------------

rem insert values into the test emp table rem the primary key should be automatically rem inserted by the emp_key_trigger trigger

insert into emp (lname, fname )
values ( 'STILL', 'JARED')
/

insert into emp ( lname, fname )
values ( 'TARRATS', 'RAY' )
/

insert into emp ( lname, fname )
values ( 'DILDINE', 'KEN')
/

insert into emp (id, lname, fname ) -- will cause 'ORA-20000' values (999999,'JOST','NICK');
/

insert into emp ( lname, fname )
values ( 'TAYLOR', 'BRYAN')
/

select /*+ index jemp_pk */ * from emp
order by id;

• CUT HERE -----------------------

Jared Still, Oracle DBA
RxNet, Division of Value Health
"All opinions are mine, not my employers" jared_at_valleynet.com Received on Tue Jan 23 1996 - 00:00:00 CET