Continue sequence of database records based on field "order"

Question

Continue sequence of database records based on field "order"

Navigation

#1 by (0 votes)

0

I have a question about the database.

My problem is that I do not want to be able to add a record with an existing order.

Instead I want to make room for the new record and the others increase in one order for it

I have a table in which it contains a field called orden and the data is recorded 1 by 1 (1,2,3,4,5,6,7, etc), but when inserted in the table a new record I insert an order 10 but that already exists what I want to do is that at the time of inserting the other records, add them 1 from the one that was registered.

Therefore, I would like this new order value to increase by 1 if this is the case, or the seguence that already exists, that is, will continue.

if the records are in order 55, and I put order 55, this or pass it to 56.

oracle

asked by mazhivo 30.01.2017 в 05:16

source

1 answer

Build installers for windows mac and linux electron as samples my data without the labels I keep tinymce?

score 0 · Accepted Answer

I propose two ways to do it without using triggers or stored procedures.

The first, simplest and portable, is to force the sum within a transaction whenever we introduce a new record:

-- START TRANSACTION no es necesario en PLSQL, se crea automáticamente una nueva
-- en la primera consulta SQL al comienzo del script o tras un COMMIT/ROLLBACK
UPDATE tabla SET orden = orden + 1 WHERE orden >= ...;
INSERT INTO tabla (...) VALUES (...);
COMMIT;

The problem is that, of course, the value of the field% co_of% of the highest records will rise uncontrollably.

The second consists of a block that updates the order only in case of giving error by duplicate key:

BEGIN
  INSERT INTO tabla (...) VALUES (...);
EXCEPTION
  WHEN DUP_VAL_ON_INDEX THEN
    UPDATE tabla SET orden = orden + 1 WHERE orden >= ...;
    INSERT INTO tabla (...) VALUES (...);
END;

In this case, you must define the orden field as a unique index.

You can find more details about exception handling in the official Oracle documentation .