use a column to determine display order of oracle table

Question

I have a table that represents locations on a campus. I am displaying these locations for the user in a web program, and I want to give them the ability to change the order that the locations are displayed in. I have a column for that, called ord. I now am trying to create a way for the user to update that column, and make sure that
1) there are no ord duplicates
2) that all ord values are >= 0 && < rowcount (of the table).

I figure I should do this with triggers. First, before I do the update, I use code to make sure the value is >= 0 & < rowcount of table. Then, once that value is inserted, I need to fix the duplicate it creates. What I want to do is just switch the values. However, I get the dreaded oracle mutating table error when I try this trigger:

create or replace 
trigger plantry_campus_edit_after
after update on plantry_campus
for each row
declare 
  v_pkid number;
begin
  SELECT pkid INTO v_pkid FROM plantry_campus WHERE ord = :new.ord && pkid != :new.pkid;
  UPDATE plantry_campus SET ord = :old.ord WHERE pkid = v_pkid;
end;

I'd prefer to do this in the database and not in the code. Any suggestions as to how I can accomplish this?

score 1 · Accepted Answer · answered Oct 07 '11 at 19:48

1

I'd change your web interface so they could re-order them on the screen, then when they save, just overwrite the ord column in every row with the order that they now appear on the screen in.

answered Oct 07 '11 at 19:48

Gerrat

28,863
9
73
101

score 0 · Answer 2 · edited May 23 '17 at 11:47

If you want to do this, try using a package variable with a plsql table type. Populate the plsql table in the row trigger, then do the update in the statement trigger. This will get round the mutating table problem, as statement level triggers aren't affected in this scenario.

Similar to this.

problem with trigger in oracle

Remember, package variables persist throughout the session, so initialize them in the before trigger.

score 0 · Answer 3 · answered Oct 08 '11 at 11:53

You can use a view to easily get around the mutating table problem; though it's not pretty. Update the view and have the trigger on that:

You will need to expand the trigger to include all the columns you're going to update.

create or replace view v_plantry_campus_edit as select * from plantry_campus;


create or replace 
trigger plantry_campus_edit_after
after update on v_plantry_campus
for each row
declare 
  v_pkid number;
begin
  SELECT pkid INTO v_pkid FROM plantry_campus WHERE ord = :new.ord && pkid != :new.pkid;
  UPDATE plantry_campus 
     SET ord = :old.ord
         -- etc
       , col1 = :new.col1
   WHERE pkid = v_pkid;
end;

score 0 · Answer 4 · answered Oct 11 '11 at 17:24

IMHO, using a trigger in this case is not a good idea, as @Ben says. I'd reorder the table in the application side like this:

The obvious:

UPDATE plantry_campus SET
  ord = :new_order
where pkid = :pkid

And the reorder updates:

If (:new_order > :old_order) then    
  UPDATE plantry_campus SET
    ord = ord + 1
  where pkid != :pkid
    and ord >= :new_order
    and ord < :old_order;
elsif (:new_order < :old_order) then
  UPDATE plantry_campus SET
    ord = ord - 1
  where pkid != :pkid
    and ord > :old_order
    and ord <= :new_order;
end if;

If you don't want to do this in the application, just put a PL/SQL function in a package.

use a column to determine display order of oracle table

4 Answers4