Re: can I use sequence in trigger ?

From: Andrew Velichko <andrew_velichko_at_yahoo.com>
Date: Thu, 28 Dec 2000 16:16:03 -0500
Message-ID: <s5O26.103224$Z2.1262511@nnrp1.uunet.ca>

Hi Ronald!

Yes, you can use sequence in triggers.
The matter is that in PL/SQL you can't just assign sequence.nextval to a variable. The following should work fine:
> create or replace trigger trg_refno_ein
> before insert on einnahmen
> for each row

declare
n number;
> begin

select seq_refno_ein.nextval into n from dual; :new.refno :=n;
> --:new.refno :=seq_refno_ein.nextval;
> end;
> /

Andrew Velichko
Brainbench MVP for Oracle Developer 2000 http://www.brainbench.com

"Ronald Mohr" <mail_at_ronald-mohr.de> wrote in message news:3A4BA6E5.6070108_at_ronald-mohr.de...
> Hy everybody
> I tried to fire a trigger before insert on a table to add a reference
> number to each row.
> Therefore I used a sequence that works fine, but not within the trigger.
> It says 'SEQ_REFNO_EIN.NEXTVAL not allowed in this context'.
> Heres the trigger:
>
> create or replace trigger trg_refno_ein
> before insert on einnahmen
> for each row
> begin
> :new.refno :=seq_refno_ein.nextval;
> end;
> /
>
> Is it just a simple mistake or can't I use a sequence that way ?
> ciao
> Ronald
>
Received on Thu Dec 28 2000 - 15:16:03 CST