Re: using sequences in triggers to generate identities

From: Walt <walt_askier_at_SHOESyahoo.com>
Date: Mon, 23 Apr 2007 14:44:31 -0400
Message-ID: <132pvkhkt195b70@corp.supernews.com>

Valentin Minzatu wrote:
> On Apr 23, 12:47 pm, if <filip1..._at_gmail.com> wrote:

>> ';m coming from a db2 background.
>> I was wondering if in Oracle there is any performance (or other)
>> difference between using a sequence in insert statements and using an
>> insert trigger to do the same job.

>
> The only difference is that by employing a trigger a small overhead is
> added - the equivalent of making a PL/SQL call.

There is a practical difference, assuming you're using the sequence to generate values for a primary key. If you get the next sequence number and then use it in an insert you can then use that value to select the row you just inserted. If the pk is generated automagically by the trigger you won't have access to it - and trying my_seq.nextval - 1 is asking for trouble. (c:

For application development I prefer to do it by hand and use a select to provide a confirmation message to the user. For batch inserts, I'll do a trigger.

> (btw, is somebody else using google groups here? is anybody else
> loosing his/her posts as well?)

Google groups is an archive, not a forum, even though it accepts posts. It can have latency of up to several hours before it shows new posts. I see both of yours, BTW.

//Walt Received on Mon Apr 23 2007 - 13:44:31 CDT