Re: Is There a Better Way? (Incrementing PK Column)

From: Alex Papadimoulis (alexp_at_papadimoulis.com)
Date: 06/11/04 

Date: Fri, 11 Jun 2004 14:13:03 -0400

Joe, a pleasure to be "schooled" by the expert. I've read (much of) your SQL
For Smarties and like your alternative to Adjacency Lists. The only chapter
missing was how to convince the boss to drop the painfully horrid practices
;-).

Anyways, I don't have any DDL scripts as this is a this is a general design
question. So, I'll try a real-world example from a system I developed a
while ago, but am wondering if there was a better way.

Orders have an OrderNum generated by a business rule (Yr +
DayOfYear+Type+Seq) and one or more Line Items.item number.
A LineItem has a sequentially number and zero or more Status entries.
A Status consists of a Status Code (processed, picked, delayed, etc) and a
Date.

For the LineItems table, the key was {OrderNum+ItemSeqNum}. It could not be
{OrderNum+Product} because an order could have two duplicate line items
(business rule). And Status table was {OrderNum+ItemSeqNum+StatusCode}.

Order creation was done through a number of different clients. One clients
was unwilling to use stored procedures, another wanted batch import of
orders from another system that didn't have line numbers (INSERT INTO
LineItems SELECT * FROM MyLineItems), so I ended up putting a trigger on the
Line Items table that made INSERTing the LineNum optional. It would sequence
it in the manner I had described.

So to bring back to my original question(s). Was there a better way of doing
that? In the future, how should i handle the situation where I need to
generate Sequential LineNumbers based off of a Order table? Should that be
done in client code or should it be in a Stored Pro such as
AddLineItem(@OrderNum,@prod,...) ?

Thanks. 

-- 
Alex Papadimoulis
http://weblogs.asp.net/Alex_Papadimoulis
"Joe Celko" <jcelko212@earthlink.net> wrote in message
news:e4JgUm1TEHA.1356@TK2MSFTNGP09.phx.gbl...
> >> I have a master and detail table like so, <<
>
> 1) The terms "master" and "detail" come from the old navigational
> databases, likew TOTAL, IDMS, IMS, etc.  In SQL we have "referenced" and
> "referencing" tables, which are very different.
>
> 2) Please post DDL, so that people do not have to guess what the keys,
> constraints, Declarative Referential Integrity, datatypes, etc. in your
> schema are.  Your personal language is useless to anyone else; you two
> Primary keys, while SQL allows only one per table.
>
> >> I want to add records [sic] to the Detail table like so:<<
>
> Let's get back to the basics of an RDBMS. Rows are not records; fields
> are not columns; tables are not files; there is no sequential access or
> ordering in an RDBMS, so "first", "next" and "last" are totally
> meaningless.
>
> >> To do this, I created an INSTEAD OF INSERT trigger on Detail that
> does the following:
> 1. Duplicate the inserted into a local temporary table (@ins_tmp)
>
> 2. Open a local cursor (@ins_cur) on MasterID, DetailNum of @ins_tmp,
> with DetailNum being updatable
>
> 3. Loop through the @ins_tmp and update DetailNum where appropriate to
> what it should be
>
> 4. INSERT INTO Details SELECT * FROM @ins_tmp <<
>
> Let's see, you have Triggers, cursors, loops and temp tables -- that is
> all the non-relational features you could use in T-SQL.  Why don't you
> just use an old magnetic tape file system instead?
>
> >> The idea is that DetialNum will start at 1 and count up for each
> MasterID.<<
>
> Yep!  Just like an old IMS database, with sequentail access to the
> detail record chains!!  This is so FUNDAMENTALLY wrong in an RDBMS, I am
> not sure where to begin.
>
> What is the generalized "Master_id"?  An invoice number? some industry
> standard? What?  Likewise, I would assume that a good programmer would
> use a verififable code for the details  (UPC, EAN, GTIN, etc.), but I
> would bet you are copying a paper form, line for line into an RDBMS.  I
> have seen this screw up so many, many times.
>
> >> I'd like to use (something like this) as an approach for a number of
> systems. Opinions? Ideas? <<
>
> Stop writing SQL, learn the basics and apologize to the employers you
> have hurt.  Get someone to re-design the system and to build correct
> stored procedures -- no cursors, no sequential numbering, few is any
> triggers, etc.
>
> --CELKO--
>
>
> *** Sent via Devdex http://www.devdex.com ***
> Don't just participate in USENET...get rewarded for it!

Relevant Pages

  • Re: Command line help
    ... I have a trigger on an SQL table that I want to add the ... DOS command line to open the DB and the form. ... Joe ...
    (microsoft.public.access.formscoding)
  • RE: Access Append Query to update Sequence Generator in Oracle
    ... There are two Triggers apparently for TASSIGNMENT and there doesn't appear ... to be a Trigger for TASSIGNINTERVAL ... SELECT COUNTINTO assignmentVariable10 ... Also committing should not make any difference to the sequence or trigger. ...
    (microsoft.public.access.queries)
  • Re: Update trigger
    ... nvltrigger for each column for example. ... SQL> drop trigger emp_upd_trig ... SQL> create table emp ( ... Commit complete. ...
    (comp.databases.oracle.server)
  • Re: stepping midi sequence. one chord per trigger.
    ... I've done virtually all my MIDI work in BeOS, which is why I say my ... would be to create a MIDI sequence of just the desired note-ons, ... tempo and when triggers occur. ... order of 10 milliseconds per trigger. ...
    (comp.music.midi)
  • Re: Update trigger
    ... nvltrigger for each column for example. ... record update even if we are only effectively changing one column ... SQL> create or replace trigger emp_upd_trig ... SQL> update emp ...
    (comp.databases.oracle.server)