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?

[Webinar] Streamline your web hosting managementRegister Today

x
 
FrivolousSamConnect With a Mentor Commented:
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
[Webinar] Improve your customer journey

A positive customer journey is important in attracting and retaining business. To improve this experience, you can use Google Maps APIs to increase checkout conversions, boost user engagement, and optimize order fulfillment. Learn how in this webinar presented by Dito.

 
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
All Courses

From novice to tech pro — start learning today.