Re: Using Sequence Gen in Trigger

rickryan_at_global2000.net (Rick Ryan) writes:

>Can a Sequence be used in a insert before row Trigger?
 

> :new.R_45_145_Package_ID := package_seq.nextval;

you can't use a sequence this way, it must be part of a SQL statement.

You need to define a variable within the PL/SQL block and assign a value to it, then use that value elsewhere, ie:

declare
  wk_val number;

begin

select package_seq.nextval into wk_val from sys.dual;

:new.R_45_145_Package_ID := wk_val;

You should be aware that any sequence values accessed by the trigger that are subsequently discarded because of a rollback or other failure to commit are not reused, but this behavior is true of sequences in general, since Oracle assures their uniqueness.

--
Mike Nolan
nolan_at_tssi.com