How would I write a trigger to reorder a sequence number?

I have a simple table in Oracle with three columns:

ID    NCHAR(10)
NAME  NCHAR(50)
SEQ   NUMBER

The SEQ field is 0 based and there to allow the user to drag the records and reorder the list (using jQuery sortable). I'm trying to figure out how to change all of the sequence numbers so they end up in the correct order in the database.

I started to create a trigger before update to change all of the SEQ numbers based on the 开发者_StackOverflow中文版:OLD and :NEW values, but stopped when I realized that this would cause an infinite loop when the trigger would just keep running since I am updating a bunch of records.

Is there a way I can get a trigger to carry out this behavior or should I write it manually into the code?

Thanks in advance

The easiest thing to do is to scrap the idea of using a consecutive sequence and instead update moved record to be the average of the previous and next value at the given location.

For example, say you have this list:

ID   NAME    SEQ
1111 Fred    10
2222 Bob     20
3333 Ted     30

To move "Ted" to the second space in the list, you'd set the sequence to 15 (20+10/2). If you later move someone between "Fred" and "Ted", they get a seq of 12.5. If you really felt the need, you could have an after update trigger on the whole table (rather than for each row) that set the SEQ to evenly spaced values.

Reordering the sequence through a trigger will be really difficult, especially if SEQ is UNIQUE and policed by a non-deferrable constraint. Your update would activate the trigger, which would in turn modify other rows, each update in turn would activate said trigger...

Fortunately this infinite recursion would be really hard to code in Oracle since you can't modify/query the base table in a trigger.

In a case like this, I would advise using a procedural approach. In PLSQL for example:

PROCEDURE update_sequence(p_id NUMBER, 
                          p_new_seq NUMBER) IS
   l_old_seq NUMBER;
BEGIN
   SELECT seq INTO l_old_seq FROM my_table WHERE ID = p_id;
   UPDATE my_table
      SET seq = CASE WHEN ID = p_id 
                        THEN p_new_seq 
                        ELSE seq + sign(l_old_seq - p_new_seq) 
                END
    WHERE seq BETWEEN least(l_old_seq, p_new_seq) 
                  AND greatest(l_old_seq, p_new_seq);
END;

This will place row p_id at sequence p_new_seq and all rows between the new position and the old will have their sequence either increased or decreased by 1.

use a linked list approach.

point a record to the record right in front of it in the order. then you minimize updates.