asked on

ReOrder trigger

I've been trying to write a trigger to reorganise a table after update, insert or delete, so starting off with the update:-

delimiter ;;
create trigger foo
after update on form

set @orderID=10;

SELECT `Order` from `form` where `saveID`=NEW.saveID ORDER BY `order` ASC;
for each row
begin
UPDATE `form` SET `order`=@orderID WHERE `priID`=NEW.priID;
@orderID=orderID+10;
end
;;

Open in new window

I thought this would work, but MySQL keeps kicking up an error on the SELECT line.

The table is made up of a load of rows, each grouped together by 'saveID', and I want them ordered in multiples of 10's (so I can easily put rows in between), so if the `orderID`= 10,12,15,20,25,30 after my function runs I want them to be 10,20,30,40,50,60. I thought the SELECT statement would pull all the rows out that are grouped together under `saveID` and then reorder them.

I cant find any information regarding how to use Select statements in triggers, can anyone advise me where Im going wrong?

Kent Olsen

Hi tonelm,

That is an incredibly bad idea. If the table grows to contain millions of rows, a single update could result in rewriting more than a million rows. That may work for 1 or 2 updates, but nothing that is general purpose.

A much better idea is to store the key in two column. The first column is the OrderId. The second column contains the ordinal of the sub-item.

Good Luck,
Kent

tonelm54

ASKER

Sorry, think Ive missed out something:-

priID = Primary ID, unique in the table
saveID = Groupped row ID
Order = An integer to contain the order

Although the table would contain a few thousand rows, the 'SaveID' will only group about 20 rows together so the trigger should only update the order for about 20 rows.

ASKER CERTIFIED SOLUTION

Kent Olsen

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial