Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: How do I have sequence automatically insert on record insertion
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.