asked on

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?

FrivolousSam

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;

MarkApplegate

ASKER

This can be run as a trigger?

FrivolousSam

You don't need to run this as a trigger; in fact, it's simpler not to.

MarkApplegate

ASKER

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.

ASKER CERTIFIED SOLUTION

FrivolousSam

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

MarkApplegate

ASKER

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