Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

ReOrder trigger

Posted on 2013-06-09
3
Medium Priority
?
457 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

As a database administrator, you may need to audit your table(s) to determine whether the data types are optimal for your real-world data needs.  This Article is intended to be a resource for such a task. Preface The other day, I was involved …
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
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…
Suggested Courses

610 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