Solved

ReOrder trigger

Posted on 2013-06-09
3
416 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 45

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 45

Accepted Solution

by:
Kent Olsen earned 500 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

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

Foreword In the years since this article was written, numerous hacking attacks have targeted password-protected web sites.  The storage of client passwords has become a subject of much discussion, some of it useful and some of it misguided.  Of cou…
More Fun with XML and MySQL – Parsing Delimited String with a Single SQL Statement Are you ready for another of my SQL tidbits?  Hopefully so, as in this adventure, I will be covering a topic that comes up a lot which is parsing a comma (or other…
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

809 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