Solved

MySQL Update Trigger question

Posted on 2013-11-12
5
573 Views
Last Modified: 2013-11-12
I want to track latest Price change for an item.
The table name is res, field names are price, prevprice, priceupdatedate

I need help with writing a trigger that updates the field "prevprice" with the old price when the "price" field is updated and then writes the current time stamp to the priceupdatedate field.

code example appreciated! Thanks!
0
Comment
Question by:Bang-O-Matic
  • 2
  • 2
5 Comments
 
LVL 13

Expert Comment

by:alpmoon
ID: 39642549
Actaully there are useful sample trigger code on the web:

http://dev.mysql.com/doc/refman/5.5/en/trigger-syntax.html
0
 
LVL 42

Accepted Solution

by:
Chris Stanyon earned 500 total points
ID: 39642571
This is the trigger you need:

DELIMITER $$
CREATE TRIGGER updatePrevPrice BEFORE UPDATE ON res
   FOR EACH ROW BEGIN
      SET NEW.prevprice = OLD.price;
   END;
$$

Open in new window

Make sure your priceupdatedate column is set to a timestamp datatype and is the only one in there, and it'll update automatically as the rows changes.
0
 

Author Comment

by:Bang-O-Matic
ID: 39642737
@ChrisStanyon
Thanks for the help!

Running the trigger did work for the setting the NEW.prevprice = OLD.price

The priceupdateddate column I changed to timestamp but it did not update the field when updating the price.

I tried:

SET NEW.prevprice = OLD.price, priceupdatedate = NOW();

but that didn't work either says my version of MySQL doesnt support multiple triggers with same action time and event for one table.
0
 
LVL 42

Expert Comment

by:Chris Stanyon
ID: 39642767
if you edit the trigger using the code I provided it will try and create the same trigger again so you need to delete it first. the timestamp column should update automatically but only if it's the only timestamp column in your table. that may depend on your version of mysql
0
 

Author Closing Comment

by:Bang-O-Matic
ID: 39642828
Works perfect!
Thanks so much!
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Loading csv or delimited data files to MySQL database is a very common task frequently questioned about and almost every time LOAD DATA INFILE comes to the rescue. Here we will try to understand some of the very common scenarios for loading data …
As a database administrator, you may need to audit your table(s) to determine whether the data types are optimal for your real-world data needs.  This Article is intended to be a resource for such a task. Preface The other day, I was involved …
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

758 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

22 Experts available now in Live!

Get 1:1 Help Now