It's not always convenient to use triggers to assign the primary key. Sometimes we
want to know the primary key of a new record so we can use it to insert rows into a
dependent table.
Am I right to think that the following is nearly as efficient as a trigger-based
approach?
insert into customer(customer_id, first, last)
values(customer_id_seq.nextval, 'Homer', 'Simpson');
insert into address(address_id, customer_id, street, town)
values(address_id_seq.nextval, customer_id_seq.currval, '742 Evergreen Tr.',
'Springfield');
Or is there a better way to do this?
Tim Helck
-----Original Message-----
From: Jared Still [mailto:[EMAIL PROTECTED]
Sent: Monday, July 05, 2004 9:45 PM
To: Kipp, James
Cc: 'Reidy, Ron'; Jim; DBI List
Subject: RE: Best Way to Auto Increment with Oracle
On Wed, 2004-06-16 at 08:43, Kipp, James wrote:
> >
> > Your program connects via SQL*net to the DB. At insert time,
> > you need to fetch a sequence, so you issue 'select
> > seq.nextval from dual'. Seems harmless enough until ...
> >
> > 1. The select statement must be passed across the SQL*Net connection.
> > 2. The statement must be parsed. Maybe the worst you will
> > have here is a soft parse, but non the less, you are using
> > the CPU for this.
> > 3. The statement is executed and the fetch occurs.
> > 4. The result is passed back to your program.
> > 5. You bind the value (hopefully) to your statement handle.
> > 6. You then execute the insert statement.
> >
> > If you use a BEFORE INSERT trigger to fetch the sequence, you
> > will eliminate steps 1, 2, 3, 4, 5 from the flow.
> >
>
> Great point. I think I will stay with the Before insert trigger.
>
>
I've always liked the elegance of inserting primary key values
from a before insert trigger via a sequence. I still do it
sometimes, though now I prefer not to.
When done from a trigger, you must use the
'select seq.nextval from dual' statement to get the PK value.
This is a *major* scalability killer. Easy to prove if anyone
is interested.
Or just google for it, it has been discussed quite heavily on
at least one oracle list.
Jared
