0

There is a MySQL database with the following table reviews(reviewer, product, score, title, comment, creation_date, last_modified). It is needed a trigger before insertion to check if the user has already reviewed the same product. If so, just update the fields score, title, comment and last_modified. This mean no insertion. Otherwise no operation is needed from the trigger and the review is inserted as usually.

N.B. primary key is bold, foreign key is italic

I tried something myself but I am confident enough to say it does not work:

DELIMITER /
CREATE TRIGGER update_review BEFORE INSERT ON reviews
FOR EACH ROW
BEGIN
IF ((SELECT reviews.reviewer, reviews.product FROM reviews WHERE reviews.reviewer=new.reviewer AND reviews.product=new.product)) THEN
SET old.score=new.score AND old.title=new.title AND old.comment=new.comment AND old.last_modified=new.last_modified;
END IF;
END /
DELIMITER ;
0

1 Answer 1

0

check if the user has already reviewed the same product. If so, just update the fields score, title, comment and last_modified.

No trigger! Use INSERT .. ON DUPLICATE KEY UPDATE.

INSERT INTO reviews (reviewer, 
                     product, 
                     score, 
                     title, 
                     comment, 
                     creation_date, 
                     last_modified)
VALUES (@reviewer, 
        @product, 
        @score, 
        @title, 
        @comment, 
        @creation_date, 
        @last_modified)
ON DUPLICATE KEY UPDATE score = VALUES(score),
                        title = VALUES(title),
                        comment = VALUES(comment)
                        last_modified = VALUES(last_modified);

Additionally - define creation_date field as DEFAULT CURRENT_TIMESTAMP and last_modified field as DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, then remove them from all INSERT/UPDATE queries - they will be updated automatically.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.