Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: How do I have sequence automatically insert on record insertion

Re: How do I have sequence automatically insert on record insertion

From: Christopher Beck <clbeck_at_us.oracle.com>
Date: Thu, 08 Apr 1999 21:09:04 GMT
Message-ID: <370d1a5e.13808245@inet16.us.oracle.com>


On Thu, 08 Apr 1999 18:05:23 GMT, Brian Lavender <blavender_at_spk.usace.army.mil> wrote:

>I have a table that I want a sequence automatically inserted as the key value
>when a record is inserted into it.
>
>I have the following table:
>
>parent
>---------------
>PARENTID * NUMBER
>FIRST VARCHAR2(8)
>LAST VARCHAR2(8)
>SEX VARCHAR2 (1)
>
>* - UNIQUE FIELD
>
>I created a sequence.
>
>create sequence FOO increment by 1 start with 100;
>
>I can insert a record into the table with the sql statement and it will insert
>the next value from the sequence into the table:
>
>insert into parent (PARENTID, FIRST, LAST, SEX) VALUES
>(FOO.NextVal, 'BILL','BAKER','M');
>
>Is there a way I can create a trigger which will automatically insert the FOO
>value from the sequence when I perform the insert below?
>
>insert into parent (FIRST, LAST, SEX) VALUES ('MARY','SMITHY','F');
>

Yup. Here is an example.

create or replace
trigger bi_parent_fer
before insert
on parent
for each row
begin
  select foo.nextval
    into :new.parentid
    from dual;
end;
/

hope this helps.

chris.

>brian
>-----------------
>Brian E. Lavender
>US Army Corps of Engineers -- Programmer / Systems Analyst
>Sacramento, CA (916) 557-6623
>
>-----------== Posted via Deja News, The Discussion Network ==----------
>http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own

--
Christopher Beck
Oracle Corporation
clbeck_at_us.oracle.com
Reston, VA.



Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Thu Apr 08 1999 - 16:09:04 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US