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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

MarsMCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
The Five Tenets of the Most Secure Backup

Data loss can hit a business in any number of ways. In reality, companies should expect to lose data at some point. The challenge is having a plan to recover from such an event.

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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.