Solved

Mysql Trigger to perform a calculation and insert the resulting value

Posted on 2007-03-20
6
599 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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 

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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
MySQL Insert Combined IDs from 2 Unrelated tables 9 49
Display multiple images in the row in PHP 11 77
MySQL database data submission 7 59
xampp tool 12 25
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…
This guide whil teach how to setup live replication (database mirroring) on 2 servers for backup or other purposes. In our example situation we have this network schema (see atachment). We need to replicate EVERY executed SQL query on server 1 to…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Learn how to create flexible layouts using relative units in CSS.  New relative units added in CSS3 include vw(viewports width), vh(viewports height), vmin(minimum of viewports height and width), and vmax (maximum of viewports height and width).

910 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

24 Experts available now in Live!

Get 1:1 Help Now