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)
);

On Feb 5, 3:10*pm, Digital Logic <mrodd... (AT) hotmail (DOT) com> wrote:
Post your trigger code and someone may provide some additional
insight.


David Fitzjarrell

On Feb 5, 3:10*pm, Digital Logic <mrodd... (AT) hotmail (DOT) com> wrote:
Post your trigger code and someone may provide some additional
insight.


David Fitzjarrell

On Feb 5, 3:10*pm, Digital Logic <mrodd... (AT) hotmail (DOT) com> wrote:
Post your trigger code and someone may provide some additional
insight.


David Fitzjarrell

On Feb 5, 3:10*pm, Digital Logic <mrodd... (AT) hotmail (DOT) com> wrote:
Post your trigger code and someone may provide some additional
insight.


David Fitzjarrell

On Feb 5, 4:17*pm, "fitzjarr... (AT) cox (DOT) net" <fitzjarr... (AT) cox (DOT) net> wrote:


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.

On Feb 5, 4:17*pm, "fitzjarr... (AT) cox (DOT) net" <fitzjarr... (AT) cox (DOT) net> wrote:


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.

On Feb 5, 4:17*pm, "fitzjarr... (AT) cox (DOT) net" <fitzjarr... (AT) cox (DOT) net> wrote:


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.

On Feb 5, 4:17*pm, "fitzjarr... (AT) cox (DOT) net" <fitzjarr... (AT) cox (DOT) net> wrote:


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.

On Feb 5, 4:54*pm, Digital Logic <mrodd... (AT) hotmail (DOT) com> wrote:
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 --