?
Solved

ReOrder trigger

Posted on 2013-06-09
3
Medium Priority
?
464 Views
Last Modified: 2013-06-27
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?
0
Comment
Question by:tonelm54
  • 2
3 Comments
 
LVL 46

Expert Comment

by:Kent Olsen
ID: 39233236
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
0
 

Author Comment

by:tonelm54
ID: 39233246
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.
0
 
LVL 46

Accepted Solution

by:
Kent Olsen earned 2000 total points
ID: 39234384
Hi tonelm,

You still don't want to do that, and in fact you probably can't.  If the rows for a grouped ID need to have a specific order, sort that out in the query where you read the data, or use the trigger to mark the row for late renumbering.

Perhaps the biggest issue that you've got is this becoming a recursive trigger.  Assume that a grouped ID has 5 related rows.  When the trigger fires it's mission is to modify at least 4 other rows in the same table.  And what happens when a row is changed?  This trigger fires!  

Here's a pretty good description of what will happen:

  http://stackoverflow.com/questions/9116911/recursive-mysql-trigger-not-working


Sorry for not getting back sooner but I was away for a bit.  I'm a little surprised that someone else didn't chime in here.


Kent
0

Featured Post

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
In this article, I’ll talk about multi-threaded slave statistics printed in MySQL error log file.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses
Course of the Month15 days, 20 hours left to enroll

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question