asked on

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.

ASKER CERTIFIED SOLUTION

MarsM

membership

This solution is only available to members.

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

Start Free Trial

theGhost_k8

You may update all fields in single query while you update totalview

MarsM

Small typo.
It should be BEFORE UPDATE instead of BEFORE INSERT (of course... :-( )

jnbkze

ASKER

I'll give it a try and let you know how I get along.
Thanks.

jnbkze

ASKER

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?

jnbkze

ASKER

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?

MarsM

Small oversight on my side....
Use: SET NEW.daily = ......
And: SET NEW.weekly =.......