| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: sequences
On Sun, 23 Aug 1998 14:20:01 -0400, "bubba" <brittonb_at_webt.com> wrote:
>I made a sequence for a customers table that is used for the primary key.
>
>When I insert data using an INSERT sql statement, I can use the
>sqncCustomerID.nextval syntax.
>
>However, I have an issue where we are using Access 97 to update some data.
>
>How can I set a trigger so that oracle automatically inserts the next value
>into the primary key?
>
>This is done very easily in MS SQL with the Autonumber data type.
>
This is also done very easily using a trigger in Oracle!
I'm able to get this working with MS Access 97 and Oracle8. I executed the following DDL:
CREATE TABLE foo (id NUMBER, num NUMBER);
CREATE SEQUENCE foo_id_seq;
CREATE OR REPLACE TRIGGER foo_iu BEFORE INSERT ON foo FOR EACH ROW
DECLARE
id NUMBER;
BEGIN
SELECT foo_id_seq.NEXTVAL INTO id FROM DUAL;
:new.id := id;
END;
That's all there is to it. Now, if I perform the following from
SQL*Plus:
INSERT INTO foo (num) VALUES(2000);
COMMIT;
The ID column will be automatically populated with the next value from
the sequence.
If I attach this table using MS Access, and I add rows to this table using the DataSheet view and only populate the NUM column, the ID column will automatically be populated with the sequence values.
>Can somebody tell me how to do this in Oracle? I don't want to have to teach
>my users how to write SQL insert statements just to edit and enter some
>simple data.
>
>brittonb_at_webt.com
>
>
BTW....I answered this same question in this same newsgroup on 5/1/1998. Sometime check out the Usenet archive DejaNews at http://www.dejanews.com. Chances are someone has already asked and received an answer to your question.
Hope this helps.
Thanks!
Joel
Joel R. Kallman Oracle Government, Education, & Health
Columbus, OH http://govt.us.oracle.com jkallman@us.oracle.com http://www.oracle.com
 |
 |