Solved

Mysql Trigger to perform a calculation and insert the resulting value

Posted on 2007-03-20
6
587 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
  • 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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 

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

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Suggested Solutions

Introduction In this article, I will by showing a nice little trick for MySQL similar to that of my previous EE Article for SQLite (http://www.sqlite.org/), A SQLite Tidbit: Quick Numbers Table Generation (http://www.experts-exchange.com/A_3570.htm…
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…
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

744 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now