MySQL Triggers

Hey Guys,

I'm not a DBA or SQL developer of any sort, but I have a challenge. What I want to do is update multiple fields of a row, when one of the other fields is updates.

Basically I'm tracking object views. I have a table called itemViews. It has the following fields:

obectID
totalViews
dailyViews
weeklyViews
monthlyViews
yearlyViews

using some PHP code, whenever an object is viewed, I'm updating the totalViews field.
What I'm looking for is a trigger than when an objects totalViews field is updated (increments by 1) that each of the other fields is incremented by 1. Is this possible, and if so could you post some sample code?

What I'm hoping is that this can be done with a trigger rather than having to build a larger SQL query and execute that from PHP.

Thanks in advance.
LVL 3
jnbkzeAsked:
Who is Participating?
 
MarsMConnect With a Mentor Commented:
It should be something like this (I haven't tested it though):

delimiter //
CREATE TRIGGER itemViewsUpdate BEFORE INSERT ON  itemViews
FOR EACH ROW
BEGIN
IF (NEW.totalViews=(OLD.totalViews+1)) THEN
NEW.dailyViews=NEW.dailyViews+1;
NEW.weeklyViews=NEW.weeklyViews+1;
NEW.monthlyViews=NEW.monthlyViews+1;
NEW.yearlyViews=NEW.yearlyViews+1;
END IF;
END;//
delimiter ;

Open in new window

0
 
theGhost_k8Database ConsultantCommented:
You may update all fields in single query while you update totalview
0
 
MarsMCommented:
Small typo.
It should be BEFORE UPDATE instead of BEFORE INSERT (of course... :-( )
0
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

 
jnbkzeAuthor Commented:
I'll give it a try and let you know how I get along.
Thanks.
0
 
jnbkzeAuthor Commented:
i was just taking another look at the code in the trigger. It makes sense if a new object is being inserted into the table, but what if a row is simply being updated? so on an existing row in the table I do an UPDATE statement - if I'm reading the trigger above correctly, the trigger would not trigger. Is this correct?
0
 
jnbkzeAuthor Commented:
When I try and enter the code I get an error
I reduced the number of columns to test with and also changed the name of the table and fields.

Here's the actual table:
mysql> describe imageViews;
+--------+---------+------+-----+---------+-------+
| Field  | Type    | Null | Key | Default | Extra |
+--------+---------+------+-----+---------+-------+
| id     | int(11) | NO   | PRI | 0       |       |
| views  | int(11) | YES  |     | NULL    |       |
| daily  | int(11) | YES  |     | NULL    |       |
| weekly | int(11) | YES  |     | NULL    |       |
+--------+---------+------+-----+---------+-------+
4 rows in set (0.20 sec)

Open in new window


and here's the trigger code and result.
mysql> delimiter //
CREATE TRIGGER imageViewsUpdate BEFORE UPDATE ON  imageViews
FOR EACH ROW
BEGIN
IF (NEW.views=(OLD.views+1)) THEN
NEW.daily=NEW.daily+1;
NEW.weekly=NEW.weekly+1;
END IF;
END;//
delimiter ;
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'delimiter //
CREATE TRIGGER imageViewsUpdate BEFORE UPDATE ON  imageViews
FOR EA' at line 1
mysql> clear
    -> ;
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'clear' at line 1
mysql>

Open in new window


I'm not sure what I'm doing wrong?
0
 
MarsMCommented:
Small oversight on my side....
Use: SET NEW.daily = ......
And: SET NEW.weekly =.......
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.