Re: using sequence in insert w/ select

You can try to use a trigger to fire before inserting rows. create or replace trigger before_insert_row   before insert or update on tablename
  for each row
begin
-- here you get values from sequence
end;

"sweth+oracle-misc_at_sweth.gwu.edu" wrote:

> i am attempting to run a sql script that will seed a table
> with values; the table to be seeded, however, has required created_by
> and created_date fields, the former of which is a foreign key to a
> user table. i thus need to do an insert with select from the script,
> to find out the person_id from the user table that corresponds to
> myself, since i will be running this script in multiple instances,
> and my person_id will vary from instance to instance.
> the problem i am running into, however, is that the table to
> be seeded also has a primary key that i am populating with a sequence;
> when i try to reference sequence.nextval in my insert with select, i
> get an error saying that a sequence number is not allowed in that
> location.
>
> here is a sample of the sort of statement i am trying
> to execute:
>
> insert into entity_role_info (
> ENTITY_ROLE_ID
> ,ENTITY_TYPE
> ,ENTITY_TITLE
> ,ORG_UNIT
> ,ROLE_SORT_ORDER
> ,BUILDING
> ,PERSON_ID
> ,SUITE
> ,CREATED_BY
> ,CREATED_DATE
> ) select DirectoryIDSequence.nextval
> ,'PERSON'
> ,'IS Coordinator'
> ,'Technology Services'
> ,'1'
> ,'Academic Center'
> ,min(person_id)
> ,'#B205'
> ,min(person_id)
> ,sysdate
> from person_info
> where UPPER(first_name) = 'SWETH'
> and UPPER(last_name) = 'CHANDRAMOULI'
> ;
>
> if i change this to a straight "insert into table with values"
> style statement, and hard-code in the person_id, everything works fine.
> what am i missing here?
>
> tia for any advice,
> sweth.
>
> --
> Sweth Chandramouli
> IS Coordinator, The George Washington University
> <sweth_at_gwu.edu> / (202) 994 - 8521 (V) / (202) 994 - 0458 (F)
> *

Received on Fri Apr 23 1999 - 14:49:12 CDT