Re: Parent/Child Table Design
Date: Tue, 5 Feb 2008 13:54:44 -0800 (PST)
Message-ID: <583a12cf-9097-4295-bfd7-fa3a2868b7e4@e23g2000prf.googlegroups.com>
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. Received on Tue Feb 05 2008 - 15:54:44 CST