Re: sequence trigger

Even simpler:

create or replace trigger mytrig_bier
before insert on mytable
for each row
when (new.pk is null)
  begin
  select myseq.nextval into :new.pk from dual;   end ;

In article <5ql17u$98j_at_camelot.dsccc.com>, John Strange <jstrange_at_imtn.dsccc.com> wrote:
>Try
>create or replace trigger mytrig_bier
>before insert on mytable
>for each row
>begin
> declare cursor mseq is
> select myseq.nextvale from dual ;
> begin
> if :new.pk is null then
> open mseq ;
> fetch mseq into new.pk ;
> close mseq ;
> end ;
>end ;
>
>
>Mark Aurit (mark_aurit_at_NOSPAM.mail.northgrum.com) wrote:
>: I want to use a sequence in my table, and Id like it done in a database
>: trigger.
>: I performed the following steps:
>: 1) create sequence myseq;
>: 2) create table mytable
>: (pk number(13),
>: name varchar2(30));
>: 3) create or replace trigger mytrig
>: before insert on mytable
>: for each row
>: begin
>: update mytable
>: set new.pk=myseq.nextval;
>: commit;
>: end;
 

>: when I attempted an insert, insert into mytable (name) values ('mark');
>: I got the following:
 

>: SQL> insert into mytable (name) values ('mark');
>: insert into mytable (name) values ('mark')
>: *
>: ERROR at line 1:
>: ORA-00904: invalid column name
>: ORA-06512: at line 2
>: ORA-04088: error during execution of trigger 'MARK.MYTRIG'
 

>: Appreciate any help!
>: Thanks, Mark
>: auritma_at_mail.northgrum.com
>
>
>--
>This posting represents the personal opinions of the author. It is not the
>official opinion or policy of the author's employer. Warranty expired when you
>opened this article and I will not be responsible for its contents or use.
Received on Tue Jul 29 1997 - 00:00:00 CDT