mysql - insert, if exist update using trigger

Question

I want to implement a Viewed system for my website. Here is the structure of my tables:

// table1
id | user_or_ip | post_id | date_time   // inserting new row for each viewed

// table2
id | post_id | total_viewed             // getting the number of total viewed for each post

Now I need to a trigger for insert/update table2 after insert in table1.

I think I have to use on duplicate key.

Answer 1

You can do this fairly easily.

With the following 2 example table:-

CREATE TABLE table1
(
    id  INT NOT NULL AUTO_INCREMENT,
    user_or_ip  VARCHAR(255),
    post_id INT,
    date_time   DATETIME,
    PRIMARY KEY (id)
);

CREATE TABLE table2
(
    id  INT NOT NULL AUTO_INCREMENT,
    post_id INT,
    total_viewed    INT,
    PRIMARY KEY (id),
    UNIQUE KEY post_id (post_id)
);

you can use the following trigger on table 1 to calculate the count and insert it to table 2:-

CREATE TRIGGER `trig_1` AFTER INSERT ON `table1`
 FOR EACH ROW BEGIN
INSERT INTO table2(post_id, total_viewed) 
SELECT post_id, COUNT(*)
FROM table1
WHERE post_id = NEW.post_id
GROUP BY post_id
ON DUPLICATE KEY UPDATE total_viewed = VALUES(total_viewed);
END

Note that if you are certain that there will never be an error you could just insert a count of 1 and set it to total_count + 1 in the ON DUPLICATE KEY clause. But if anything fails that prevents the trigger the counts will be forever wrong for that post_id:-

CREATE TRIGGER `trig_1` AFTER INSERT ON `table1`
 FOR EACH ROW BEGIN
INSERT INTO table2(post_id, total_viewed) 
VALUES(NEW.post_id, 1)
ON DUPLICATE KEY UPDATE total_viewed = total_viewed + 1;
END

Note also that the sub query to get the count will be more efficient with an index on post_id in table1