Re: Newbie: trigger with sequences

just an additional note:

create or replace trigger EMP_TRIG before insrt on EMP for each row begin
  IF :NEW.EMPNO IS NULL then

          select emp_ids.nextval into :new.empno from dual;   END IF;
end;

this IF statement permits reloading of the table without resequencing the primary keys (in case they are foreign keys somewhere else)

"Jim Kennedy" <kennedy-family_at_attbi.com> wrote in message news:axQW7.4659$NE5.26258_at_rwcrnsc53...
> Very close. Try:
>
> CREATE OR REPLACE trigger EMP_TRIG
> before insert on EMP
> for each row
> begin
> select emp_ids.NEXTVAL into :new.empno from dual;
> end EMP_TRIG;
> /
>
> Jim
> "Nikki Woelk" <nikki_at_faludi.com> wrote in message
> news:u2nihhio2mdn41_at_corp.supernews.com...
> > Is there anyway to create a trigger that will automatically populate the
> > primary key field with the NEXTVAL from the sequence for that table? I
> > tried something like this (with sequence emp_ids already created):
> >
> > CREATE OR REPLACE trigger EMP_TRIG
> > before insert on EMP
> > for each row
> > begin
> > :new.empno := emp_ids.NEXTVAL;
> > end EMP_TRIG;
> > /
> >
> > but it returns with an error that "Sequence reference 'EMP_IDS.NEXTVAL'
> not
> > allowed in this context".
> >
> > Thanks.
> > --
> > Nikki
> >
> >
>
>
Received on Fri Dec 28 2001 - 17:04:41 CST