I've been trying to write a trigger to reorganise a table after update, insert or delete, so starting off with the update:-
create trigger foo
after update on form
SELECT `Order` from `form` where `saveID`=NEW.saveID ORDER BY `order` ASC;
for each row
UPDATE `form` SET `order`=@orderID WHERE `priID`=NEW.priID;
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?