Mysql Trigger to perform a calculation and insert the resulting value

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?
MarkApplegateAsked:
Who is Participating?
 
FrivolousSamCommented:
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
 
FrivolousSamCommented:
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
 
MarkApplegateAuthor Commented:
This can be run as a trigger?
0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
FrivolousSamCommented:
You don't need to run this as a trigger; in fact, it's simpler not to.
0
 
MarkApplegateAuthor Commented:
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
 
MarkApplegateAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.