Solved

ReOrder trigger

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

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

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

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…
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 a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…
Attackers love to prey on accounts that have privileges. Reducing privileged accounts and protecting privileged accounts therefore is paramount. Users, groups, and service accounts need to be protected to help protect the entire Active Directory …

733 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