Re: sequence trigger

From: John Strange <jstrange_at_imtn.dsccc.com>
Date: 1997/07/17
Message-ID: <5ql17u$98j@camelot.dsccc.com>#1/1

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 Thu Jul 17 1997 - 00:00:00 CDT