Re: Sequence Question

From: Eugene Freydenzon <efreydenzon_at_corpinfo.com>
Date: 1996/09/13
Message-ID: <32399033.4E9E_at_corpinfo.com>#1/1

David J Roth wrote:
>
> I need to set up a table so that a unique key gets populated automatically.
> It would be nice if I could use a sequence in a default declaration but this is not allowed.
> I thought I could use a sequence in a trigger but this does not seem to work either.
>
> I would appreciate any suggestions for other approaches.
>
> Dave Roth

Trigger should work:
create table test (test_id number(8) primary key,

		   test_text varchar2(40) not null,
		   created_by varchar2(30),
	           created_date date);

create sequence test_seq MAXVALUE 99999999; create or replace trigger test_tr

	before insert 
	on test 
	for each row 

begin
select test_seq.nextval, user, sysdate
 into :new.test_id, :new.created_by, :new.created_date  from dual;
end;
/

result:
SQL> insert into test(test_text) values ('wwww');

1 row created.

SQL> select * from test;

  TEST_ID TEST_TEXT                               
CREATED_BY                     CREATED_D
--------- ----------------------------------------
------------------------------ ---------
        1 wwww                                    
TEST1                         13-SEP-96

Note: Always make sure column datatype match MAXVALUE in sequience.

I hope it helps.
Eugene.
P.S. If you can not compile the trigger, contact me.

-- 
********************************************************
*        Everything above is only my opinion           *
********************************************************
*						       *
* If you see a lion in a cage and sign says "elephant" * 
*                      ,                               *
*           DO not belive your eyes !                  *
*       (Kozma Prutkov. (informal translation))        *
*                                                      *
********************************************************

Received on Fri Sep 13 1996 - 00:00:00 CEST