Re: Sequences?

Your solution is correct but incomplete as stated. If you *do* fire a value at an identity column/autonumber then it updates the identity.nextval appropriately if the value that you supply is larger than the current identity.nextval. So the full trigger implementation has to check if there is an incoming value for the identity column, use it or get sequence.nextval as appropriate. If there is an incoming value it may need to alter sequence.nextval, but I couldn't find the Oracle syntax for doing that so I had to drop and re-create the sequence with its appropriate nextval. I wasn't sure if this would work because of mixing DDL into trigger code and I was concerned (possibly incorrectly, I didn't care to find out) about transaction safety and speed so I wrote a separate SP that examines *all* of the trigger-sequences (which are identifiable via automatically mangled names that my DDL constructer gives them that identify their table and column), and "create or replaces" them as necessary where max(table.colvalue)>sequence.nextval. Someone has to remember to run this in certain situations such as after a mass data upload from another db. Not a desirable way to run things. AIH SQL Server supplies the identity-analog of this SP (DBCC CHECK IDENT which in 6.5 needed to be run quite often because of a caching bug in SQL Server that incredibly hadn't been fixed despite being identified in 4.5, thanks BillG, now that one gave me SERIOUS grief) I've never had to run it in 7 or 2000, and nor does any other platform I've seen offer this. It should never be necessary in a bug-free implementation of identities.

Granted all of this Oracle sequence/trigger/SP shennanigans only took a day or two, Oracle being unfamiliar to me so I had to experiment a bit to find correct syntax. I'm sure an experienced DBA would have had the db-side programming down in half an hour (the thinking time about the issues being irreducible except for the very clever <G>). Then of course there was the modifying of my cross-platform DDL generator to create the appropriately named sequences and the triggers in the first place.....Luckily its design is flexible enough to achieve this. I'd rather have spent the time doing something useful.

Is this still "whining"? Or was it a genuine problem caused by Oracle's lack of identities that had to be solved with great care to avoid error? Ah well....

Regards

"damorgan" <dan.morgan_at_ci.seattle.wa.us> wrote in message news:3C72834F.E405138_at_ci.seattle.wa.us...
> I disagree with your premise. What most developers do when there are
multiple sources of
> import is write a before-insert trigger. That way there is no way for the
sequence to
> not be used appropriately. Now I'll grant it is a few more keystrokes than
29 ... but
> then again it looks a lot better on your resume too so you get a payback.
>
> PS: There is no perfect database just as there is no other perfect tool. I
don't care
> how wonderful a hammer is ... you can't use it to unscrew a bolt.
>
> Daniel Morgan
>
>
>
> Tony Berrington wrote:
>
> > Daniel,
> > I don't think the point is the difficulty of creating the
sequence in the
> > first place, or in using it.
> >
> > The big problem arises when you have several potential sources of
inserts/updates to
> > your data. You cannot guarantee that they will all use the sequence (and
some may
> > actually not be able to).
> >
> > If you are using it for something as important as key generation, then
you have a
> > very significant issue.
> >
> > By the way, I am an Oracle fan myself. It's a very good database, but
not a perfect
> > one.
> >
> > Tony Berrington
> >
> > damorgan wrote:
> >
> > > That you haven't needed the capabilities provided by Oracle's
sequences is fine.
> > > Some people, many people perhaps, don't. But some of us do. And for us
...
> > > autonumbering would be useless. So are you saying you'd rather have
something
> > > that limits you to only one possible usage ... or would you rather
have
> > > something robust, flexible, and scalable?
> > >
> > > But I must state that I find the amount of whining over this to be
amazing. Is
> > > creating and using a SEQUENCE more difficult than creating an
autonumbered
> > > column? Lets see:
> > >
> > > SQL> CREATE SEQUENCE s;
> > >
> > > The total characters typed was 18 including spaces and the semicolon.
that
> > > created the sequence. I'm a bit sore but I'll continue.
> > >
> > > Now I'll add the sequence number to the insert statement for the
table:
> > >
> > > s.NEXTVAL,
> > >
> > > Well that took a total of 10 keystrokes including the comma at the
end. Probably
> > > 11 if you hit the space bar before continuing with the rest of the
insert
> > > statement.
> > >
> > > A total of 29 keystrokes.
> > >
> > > Damn I guess you are correct ... my fingers are hurting and I'm
developing some
> > > kind of syndrome that is going to require extensive use of pain
medication,
> > > antiinflammatory steroids, and physical therapy.
> > >
> > > Daniel Morgan
> > >
> > > Heinz Kiosk wrote:
> > >
> > > > > There is no relationship between MS Access's autonumbering and a
SEQUENCE.
> > > > The
> > > > > autonumering is part of the table and is only capable of
sequential
> > > > numbering.
> > > > > It has no flexibility, no programmability, and is strictly tied to
a
> > > > single
> > > > > table. All it is is the following code built in and hidden from
the end
> > > > user.
> > > > >
> > > > > SELECT MAX(numbering_field)
> > > > > INTO next_number
> > > > > FROM xyz;
> > > > >
> > > > > INSERT INTO xyz
> > > > > (numbering_field + 1, other_field1, other_field2)
> > > > > VALUES
> > > > > (next_number, someval1, someval2);
> > > > >
> > > > > Daniel Morgan
> > > >
> > > > Methinks Daniel doth protest too much ;). "All Autonumber/Identity
is" is
> > > > something exteremely useful that answers 99% of needs for this kind
of
> > > > thing. I've never wanted numbers that cut across tables and I've
never
> > > > wanted anything other than incremental numbering in 20 years of db
schema
> > > > design. I agree "create sequence" is more flexible than MS SQL
identity or
> > > > MS Access autonumber or DB2 identity or Sybase....(long boring list
of rival
> > > > technologies snipped); but sequences are also a pain in the arse
when all
> > > > you want is a system generated identity (as supplied in easier form
by every
> > > > other db platform I've ever seen). Also are you seriously suggesting
that
> > > > the above is the algorithm that any db actually uses? I think not.
> > > > (particularly as you wrote it wrong, SQL syntax error. Also
potential
> > > > problems with above algorithm with transactions and synchronicity.
Never
> > > > mind)
> > > >
> > > > Regards
>
Received on Wed Feb 20 2002 - 04:04:10 CST