Re: sequence.nextval out a trigger

In article <373ibl$p8r_at_aragorn.unibe.ch>, acherman_at_asterixiam.unibe.ch (Franz Achermann) writes:
|> Hi,
|>
|> I'd like to have a before insert for each row trigger,
|> which inserts automatically keys. But what am I doing wrong
|> in the following example:
|>
|> CREATE TABLE Personal (
|> Per_Nr number(6) PRIMARY KEY,
|> Per_Name varchar(20) not null
|> );
|>
|> CREATE SEQUENCE key_generator;
|>
|>
|> CREATE TRIGGER insert_personal
|> BEFORE INSERT ON Personal
|> FOR EACH ROW
|> DECLARE v number(6);
|> BEGIN
|> :new.Per_Nr := key_generator.nextval;
|> END;
|> /

You can't assign sequence.nextval to a variable - you have to select it from dual, as in

  select key_generator.nextval
    into :new.Per_Nr
    from dual;

Assignment is a procedural statement, and currently seqeunces can only be used in SQL statements, like select or insert.

|>
|> The sqlplus interpreter simply says:
|> Table,View Or Sequence reference 'KEY_GENERATOR' not allowed in this context
|>
|> What am I missing or is it really not possible to use sequences
|> out a triggerbody. May I really ask all the users to type:
|>
|> INSERT INTO Personal VALUES(key_generator.nextval, 'text');
|>
|> Any Hints are welcome
|>
|> Franz Achermann (acherman_at_iwi.unibe.ch)
Received on Sat Oct 08 1994 - 00:42:58 CET