Hello Everybody,

I have a table like the one below;

LINES
CUSTOMER integer
INVOICE integer
SEQUENCE integer

This has a unique ascending index (LINE_INDEX) across the fields customer,
invoice and sequence. I have a trigger on this table firing AFTER DELETE
that renumbers the sequence. So, if I had the following data;

CUSTOMER INVOICE SEQUENCE
1 23 1
1 23 2
1 23 3
1 23 4

and then deleted the second row (customer = 1, invoice = 453 and sequence =
2), I'd like records 3 and 4 to be updated with the following statement,
which lives in the trigger.

UPDATE LINESSET LINES.SEQUENCE = (LINES.SEQUENCE- 1)
WHERE (CUSTOMER = old.CUSTOMER) AND
(INVOICE = old.INVOICE) AND
(SEQUENCE > old.SEQUENCE);

I get an error - 'Invalid insert or update value(s): object columns are
constrained - no 2 table rows can have duplicate column values. attempt to
store duplicate value (visible to active transations) in unique index
'LINE_INDEX'.

To get around the problem, I've created a procedure that the trigger calls
that goes through the applicable records one by one, starting at the
bottom. Is there any other way?

Thanks,

Peter Lee


-
Peter Lee Mobile: +61 412 011 174 ptle@...
-----------------------------------------------------------------------
Rising Software Australia Pty. Ltd. http://www.risingsoftware.com/
Publishers of 'Auralia' - Ear Training and 'Musition' - Theory Training
Ph: +61 3 9481 3320 FAX: +61 3 9481 3380 USA Freecall: 1 888 667 7839