Here's the table schema (Fiddle)
Is it possible to create a trigger on INSERT INTO ON DUPLICATE KEY UPDATE
statement?
Here's the query that allow users to update their like or dislike for a product.
Updated:
$vote = $_POST["vote"]);
$query .= "INSERT INTO `product_review` (ID,user,toyid,liketype) VALUES (1,Tom,1,1)";
if($vote == 'likes')
{
$query .= "ON DUPLICATE KEY UPDATE liketype='1'";
}
else
{
$query .= "ON DUPLICATE KEY UPDATE liketype='0'";
}
After the query, I want to add a trigger on table reviews
to update like/dislike field.
I've tried
CREATE TRIGGER 'after_like_update' AFTER INSERT OR UPDATE ON 'product_review'
but the syntax is wrong.
The following trigger only works when a new row is inserted:
delimiter //
CREATE TRIGGER `after_like_update`
AFTER INSERT ON `product_review`
FOR EACH ROW BEGIN
UPDATE toy a,
(
SELECT SUM( like_type =1 ) AS likes, SUM( like_type =0 ) AS dislikes
FROM `product_review`
WHERE post_id = NEW.post_id
)src
SET a.likes = src.likes,
a.dislikes = src.dislikes WHERE a.id = NEW.post_id;
END;
//
DELIMITER ;
Table:
CREATE TABLE toy
(`ID` int, `toy` varchar(21), `like` int ,`dislike` int)
;
INSERT INTO toy
(`ID`,`toy`, `like`,`dislike`)
VALUES
(1, 'ToyA', 2, 0),
(2, 'ToyB',0 , 0),
(3, 'ToyC', 0, 0),
(4, 'ToyD', 1, 2),
(5, 'ToyE', 0, 0),
(6, 'ToyF', 0, 0),
(7, 'ToyG',0, 1),
(8, 'ToyH',0, 1),
(9, 'ToyI', 0, 0)
;
CREATE TABLE product_review
(`ID` int,`user` varchar(20), `toyid` varchar(21), `liketype` int)
;
INSERT INTO product_review
(`ID`, `user`,`toyid`, `liketype`)
VALUES
(1, 'Tom','1', 1),
(2, 'Ben', '4',0),
(3, 'Peter','1', 1),
(4, 'May','4', 0),
(5, 'May', '8',1),
(6, 'Tom','7',0),
(7, 'Paul','4', 1)
;