Solved

Mysql Trigger to perform a calculation and insert the resulting value

Posted on 2007-03-20
6
630 Views
Last Modified: 2008-01-09
In MYSQL
Lets say I have a table mags
with fields - REVDate, name, and Printorder

On an insert containing  REVDate and name

 I want to use a trigger (with or without a  procedure/function) to do the following:

Select then max(printorder) from mags where REVDate = New.REVDate;

Add 1 to the max(printorder) selected

 and then set Printorder for this insert to this new value.

so if the max(printorder) in the database is 5  on a new insert :
REVDate = June and name = John...the printorder would end up being 6

Can anyone help me with how to do this?
0
Comment
Question by:MarkApplegate
[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
  • 3
  • 3
6 Comments
 
LVL 6

Expert Comment

by:FrivolousSam
ID: 18759518
Use a SELECT subquery to find the maximum in Printorder.  You will have to lock the table because you don't want another user to create a race condition and cause you to insert rows with duplicate values for Printorder.

In this example, you would replace '2007-03-20' and 'Me' with the variables from your script, or use the template to create a parameterised query.

LOCK TABLES mags WRITE;

INSERT INTO mags (REVDate, name, Printorder)
VALUES ('2007-03-20', 'Me', (SELECT MAX(Printorder) FROM mags)+1);

UNLOCK TABLES;
0
 

Author Comment

by:MarkApplegate
ID: 18760806
This can be run as a trigger?
0
 
LVL 6

Expert Comment

by:FrivolousSam
ID: 18763241
You don't need to run this as a trigger; in fact, it's simpler not to.
0
Webinar: Security & Encryption in the MySQL world

Join Percona’s Solutions Engineer, Dimitri Vanoverbeke as he presents “Security and Encryption in the MySQL world” on Thursday, July 6, 2017 at 7:00 am PDT / 10:00 am EDT (UTC-7).

 

Author Comment

by:MarkApplegate
ID: 18763357
I have multiple users interfacing with this database through a php web based interface.  When they click the submit button to INSERT revdate and name (along with alot of other table entrees that were unimportant for this explanation) I need to do what is described above.  

Get the max value for printorder where the values in the column revdate = the submited revdate and then increment the max printorder value by one.  Then perform the insert with this new printorder value along with revdate, name,........

I could do this all in the php code, and have in the past; however, I was directed to use triggers for this.
0
 
LVL 6

Accepted Solution

by:
FrivolousSam earned 500 total points
ID: 18763562
I would suggest you put the code above into a stored procedure, and then just call that procedure on the submit button -- it will be simpler than creating a trigger.  A trigger is not the ideal way to achieve what you want.

Use the following code:

DELIMITER ||
CREATE PROCEDURE insertMags (IN revdate DATE, IN name VARCHAR(255))
BEGIN
      LOCK TABLES mags WRITE;
      INSERT INTO mags (REVDate, name, Printorder)
      VALUES (revdate, name, (SELECT MAX(Printorder) FROM mags)+1);
      UNLOCK TABLES;
END;
||
0
 

Author Comment

by:MarkApplegate
ID: 18763652
Great that looks like what I'm looking for.  

And I suppose if the Powers really want a trigger I could create a trigger that before insert executes the above procedure ;)

Thanks FrivoulousSam
0

Featured Post

What Is Transaction Monitoring and who needs it?

Synthetic Transaction Monitoring that you need for the day to day, which ensures your business website keeps running optimally, and that there is no downtime to impact your customer experience.

Question has a verified solution.

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

I have been using r1soft Continuous Data Protection (http://www.r1soft.com/linux-cdp/) for many years now with the mySQL Addon and wanted to share a trick I have used several times. For those of us that don't have the luxury of using all transact…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

689 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