Re: Help! Trigger can't set primary key.

Sandy Koczko <slk_at_aoics.com> wrote:

>I have a table, cvslog, with a numeric column, id, which I want
>to make the table's primary key; i.e. I want rows in the table to
>be identified by a serial number. I want the value to be assigned
>by a trigger selecting the next value from a sequence (so I only
>ever have to do it in one place). This seems to me to be a
>reasonable thing to do.

Here is a slightly modified version of your script. It worked on 7.0.16.

create table cvslog (

        id                number not null,
        ecd_id            number          ,
        userid            varchar2(20)    ,
        logdate           date            ,
        action            char(3)         ,
        state             char(3)         ,
        topath            varchar2(255)   ,
        frompath          varchar2(255)   ,
        revision          varchar2(30)    ,
constraint
        check_action      check (action in ('INS', 'UPD', 'DEL')),
constraint
        check_state       check (state in ('DEV', 'TST', 'REL','TAG'))

alter table cvslog add constraint cvslog_prim primary key(id);

create sequence cvslog_seq;

create or replace trigger cvslog_ins_trig before insert on cvslog
for each row
begin

        select cvslog_seq.nextval into :new.id from dual; end;

/

• test it insert into cvslog(ecd_id) values(1);

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