Re: Oracle Primary key and Delphi: How to insert the primary key value automatically ?

From: Ken Nichols <knichols_at_mcsilo.ilo.dec.com>
Date: 1997/09/22
Message-ID: <34268A96.48E@mcsilo.ilo.dec.com>#1/1

Ger,

I maintain such triggers within S-Designor, a case tool. The case tool substitutes a generated name for %TRIGGER% and the current table name for %TABLE%. Here is how one looks:

• Trigger Name: %TRIGGER%
• Project: RCM
• Author: Ken Nichols
• Trigger Type: %TRGTYPE%
• This trigger was generated from within
• S-Designor
• Its purpose is to generate a unique
• number to serve as the key for insertion of
• of rows into %TABLE%

create trigger %TRIGGER% before insert
on %TABLE% for each row
declare

    integrity_error exception;

    errno            integer;
    errmsg           char(200);
    dummy            integer;
    found            boolean;

    v_CpuID %TABLE%.CpuID%TYPE;
    v_CollectionID CollectionDetails.CollectionID%TYPE;

begin

• Get the next sequence value for the current table SELECT %TABLE%_seq.nextval, CollectionDetails_seq.currval INTO v_CpuID, v_CollectionID FROM dual;

    :new.CpuID := v_CpuID;
    :new.CollectionID := v_CollectionID;

• Error handling exception when integrity_error then raise_application_error(errno, errmsg); end; /

Hope this helps,

Ken

Ger Otten wrote:
>
> At the moment I am developing a client/server application with delphi 3
> Client/Server and Oracle 7.3.3.
> To make the application as fast as possible I want to use numeric primary
> keys.
>
> Oracle has the possibilty to generate sequence numbers that can be used as
> the value for the primary key in a table.
> For instance:
>
> Create sequence order_seq
> start with 1
> increment by 1
> nocylce
> cache 20;
>
> At the moment I insert a new record in a table I can reference the sequence
> number with NEXTVAL.
> For instance:
>
> Insert into orders (orderno, custno)
> values (order_seq.NEXTVAL, 1032)
>
> Questions:
>
> 1. Is it possible to use Oracle Database triggers to automatically fill in
> the next sequence number in the primary key field ?
> If so how can this be achieved ?
>
> 2. If this is not possible I would like to know the following : When I have
> a table open in a grid open in Delphi and I am in the edit
> mode. When I insert a record can I then get the next sequence number from
> Oracle and fill it myself in the table ? How do I do that.
> 3. Maybe both are possible: What do you prefer ?
>
> Thank you very much in advance !!
>
> sincerely,
>
> Ger Otten
>
> email: gmjotten_at_worldonline.nl

> Received on Mon Sep 22 1997 - 00:00:00 CDT