Solved

ReOrder trigger

Posted on 2013-06-09
3
413 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:Kdo
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:
Kdo 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

All XML, All the Time; More Fun MySQL Tidbits – Dynamically Generate XML via Stored Procedure in MySQL Extensible Markup Language (XML) and database systems, a marriage we are seeing more and more of.  So the topics of parsing and manipulating XM…
I use MySQL for many of my development projects in a Windows environment. To manage my databases (and perform queries) for years I used a tool called MySQL administrator.  This tool has since been replaced by MySQL Workbench. So I decided to m…
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …
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.

776 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