Re: using sequences in triggers to generate identities

On Apr 23, 2:44 pm, Walt <walt_ask..._at_SHOESyahoo.com> wrote:
> 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:
>

The same can be accomplished by either:
* INSERT INTO ... RETURNING <the key value> INTO <variable>; or
* SELECT my_seq.CURRVAL ... -- after the insert was completed

> 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.
>

For batch inserts, you may want to do it somehow different: using bulk insert so you don't make an extra PL/SQL call for each row.

> > (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

I see both my posts now - somehow disconcerting, as I can't see a post I made days ago.

Valentin Received on Mon Apr 23 2007 - 14:19:06 CDT