Re: Generating unique key numbers

From: Jonathan Gennick <gennick_at_worldnet.att.net>
Date: 1997/12/02
Message-ID: <6600s1$n4d@bgtnsc01.worldnet.att.net>#1/1

On 29 Nov 97 15:39:10 GMT, "Michal Motalík" <cross_at_zl.inext.cz> wrote:

>I can not create trigger:
>
>CREATE TRIGGER tabtrig
>BEFORE INSERT ON tab
>FOR EACH ROW
>BEGIN
>  :NEW.id := tabseq.NEXTVAL;
>END;
>
>The sequence "tabseq" was created.
>I get the error PLS-00357.
>(reference to sequence is not permitted in these context)
>
>Does anybody know where the problem is?

To get a sequence value in Oracle, you have to select from dual. You need to do something like:

create trigger tabtrig
before insert on tab
for each row
DECLARE
   nextkey number;
BEGIN
  select tabseq.nextval into nextkey from dual;   :new.id := nextkey;
END;

---

Jonathan Gennick
gennick_at_worldnet.att.net
http://home.att.net/~gennick Received on Tue Dec 02 1997 - 00:00:00 CST