RE: Oracle and dbh -> last_insert_id
- From: stbaldwin@xxxxxxxxxxxxxxxx (Steve Baldwin)
- Date: Fri, 7 Oct 2005 09:50:05 +1000
Hi Ron,
See below ...
-----Original Message-----
From: Ron Savage [mailto:ron@xxxxxxxxxxxxx]
Sent: Friday, 7 October 2005 9:09 AM
To: List - DBI users
Subject: RE: Oracle and dbh -> last_insert_id
On Fri, 7 Oct 2005 08:24:22 +1000, Steve Baldwin wrote:
Hi Steve
> (Apologies for top-posting ...)
No problem. It's the norm in the mod_perl list, but still distresses some
people.
> In most cases there will be no difference, but it is possible for
> triggerbased logic to subsequently bump the value of a sequence.
> This means fetching seq.currval after the insert could give you a
> value higher than the one actually used in the insert.
Right, or another insert by another user before the call to currval.
[SB] Not quite right. currval returns the last value from nextval *only
from your session*. If another session calls nextval it has no impact on a
call to currval from your session. What I was referring to was additional
invocations of nextval in trigger logic fired from your insert. Unlikely
but possible.
Since I wrote all the code there are definitely no triggers (yet).
[SB] Emphasis on the 'yet'. You need to have a mechanism in place that
isn't going to break when someone else mucks with this down the track.
> What we do is have the primary key populated in a BEFORE INSERT
> trigger, and then (as suggested) use the RETURNING clause in the
> insert. This is the only way to *guarantee* you are getting the
> correct value.
I assume this is calling nextval and then using that value in the insert.
[SB] Yep
> If you need more info, let me know and I will provide an example.
Yes, please. I've never used a trigger, and even if they are simple, I'd
rather
not stuff things up.
[SB] Something like this ...
CREATE OR REPLACE PACKAGE Global_P AS
--
Some_Sequence PLS_INTEGER;
--
END Global_P ;
/
CREATE OR REPLACE TRIGGER SomeTable_BIR01
BEFORE INSERT ON SomeTable
FOR EACH ROW
DECLARE
l_ID PLS_INTEGER;
BEGIN
SELECT Some_Sequence.NEXTVAL
INTO l_ID
FROM dual;
--
:NEW.ID_Column := l_ID;
Global_P.Some_Sequence := l_ID;
END;
/
Then in the code after the insert ...
my $sth = $dbh->prepare('BEGIN :id := Global_P.Some_Sequence; END;');
$sth->bind_param_inout(':id', \$id, 20);
$sth->execute;
# $id now hold the value of the sequence used to
# populate SomeTable.ID_Column
I haven't tested the above code, but it should be pretty close.
In general, we try to put as much business logic as possible down at the
Database level via triggers. It's not totally bulletproof as triggers can
be dropped and disabled, but it means the business logic is as much as
possible in one place, rather than interspersed throughout your application.
So, even if a particular table can be populated from 2 or more modules (not
counting someone hacking stuff from SQL*Plus !!), the business logic is in
one place, and as long as no-one has dicked with the triggers, will be
invoked regardless of the source of the operation.
Hope this helps,
Steve
Email directly as per below if you think that's appropriate.
--
Cheers
Ron Savage, ron@xxxxxxxxxxxxx on 7/10/2005
http://savage.net.au/index.html
Let the record show: Microsoft is not an Australian company
.
- References:
- RE: Oracle and dbh -> last_insert_id
- From: Ron Savage
- RE: Oracle and dbh -> last_insert_id
- Prev by Date: RE: Oracle and dbh -> last_insert_id
- Next by Date: Re: Perl DBI and threads.pm
- Previous by thread: RE: Oracle and dbh -> last_insert_id
- Next by thread: Perl DBI and threads.pm
- Index(es):
Relevant Pages
|
