Re: using sequence in a trigger

From: Suzanne Edgecombe <Suzanne.Edgecombe_at_agso.gov.au>
Date: Fri, 22 Oct 1999 12:15:12 +1000
Message-ID: <7uohap$t088@thredbo.dpie.gov.au>

Hi there,

you need to SELECT the next value from the sequence like so:

CREATE OR REPLACE TRIGGER ozrivers.new_estuaryno BEFORE INSERT
ON ozrivers.estuaries
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
Begin

     if :new.estuaryno is null then
     begin
          select seq_estuaryno.nextval
          into :new.estuaryno
          from dual;
     exception when others then null;
     end;
     end if;

End;
/

Hope this helps!!

cheers
ccyr6798_at_my-deja.com wrote in message <7uo3kn$ogb$1_at_nnrp1.deja.com>...
>I want to use a sequence (met_id_seq) for a unique id (met_id) on a
>table. I'd like to have an insert trigger take care of that column for
>me...
>
>create or replace trigger ml_metlab_trg
> before insert or update or delete on ml_metlab
> referencing new as new old as old
> for each row
> begin
> if inserting then
> :new.met_id := met_id_seq.nextval;
> end if;
> :
> :
>
>But I get an error when creating the trigger.
>PLS-00357: Table,View Or Sequence reference 'MET_ID_SEQ.NEXTVAL' not
>allowed in this context
>
>what am I missing?
>Thanks,
>Cary
>
>
>Sent via Deja.com http://www.deja.com/
>Before you buy.
Received on Thu Oct 21 1999 - 21:15:12 CDT