Re: Parent/Child Table Design

On Feb 5, 4:54 pm, Digital Logic <mrodd..._at_hotmail.com> wrote:
> On Feb 5, 4:17 pm, "fitzjarr..._at_cox.net" <fitzjarr..._at_cox.net> wrote:
>
>
>
>
>
> > On Feb 5, 3:10 pm, Digital Logic <mrodd..._at_hotmail.com> wrote:
>
> > > A bit a newbie to Oracle so I apologize if this is a simple question.
> > > I need to implement two tables similar to the classic order and order
> > > details example.  I've place a rough description of the two tables
> > > below.  How would I autogenerated values for the OrderLineNumber
> > > column given that that should be unique for each order and not for
> > > each order line.  I experimented with using triggers to generated the
> > > numbers based on the records already present in the table.  I put
> > > together a trigger that works as long as you're only insert a single
> > > row at a time, but does not work for multiple rows.  It would
> > > appreciated if anyone has a solution.
>
> > > Orders(
> > >   OrderID integer primary key,
> > >   ......
> > > );
>
> > > OrderLines(
> > >   OrderID integer not null,
> > >   OrderLineNumber integer not null,
> > >   ....
> > >   Constraint OrderLines_PK
> > >     Primary Key (OrderID, OrderLineID),
> > >   Constraint OrderLines_FK
> > >     Foreign Key (OrderID)
> > >     References Orders(OrderID)
> > > );
>
> > Post your trigger code and someone may provide some additional
> > insight.
>
> > David Fitzjarrell
>
> CREATE OR REPLACE TRIGGER BI_OrderLines
> BEFORE INSERT ON OrderLines
>     FOR EACH ROW
>     WHEN (NEW.OrderLineNumber IS NULL)
>     DECLARE
>         PRAGMA AUTONOMOUS_TRANSACTION;
>     BEGIN
>         SELECT
>             Coalesce(Max(OrderLines.OrderLineNumber), 0) + 1
>             INTO :NEW.OrderLineNumber
>         FROM OrderLines
>         WHERE OrderLines.OrderID = :NEW.OrderID;
>         COMMIT;
>     END;
>
> The problem when inserting mutlitple lines seems to be that the
> OrderLineNumber calculated is the same for each record since the
> preceeding records are not committed to the table yet.  I thought that
> if I could access the rownum  pseudocolumn I could determine how many
> records were inserted prior to the record being processed and base the
> new OrderLineNumber on this as well, but apparently the column is not
> accessable in this context.- Hide quoted text -
>
> - Show quoted text -

I suggest you consider using a second sequence for the line numbers. Who cares if the line numbers are not sequential or in the thousands? Most order entry systems include a validation step at the end of the order entry process; you can order and renumber the line items for storage at this point if desired. While data is being entered you can number the details on the screen and have the logic take care of updating the real line items.

HTH -- Mark D Powell -- Received on Tue Feb 05 2008 - 17:52:38 CST