MySQL trigger: if record exists just update

insertMySQLtrigger

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 ;

Best Answer

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.

Related Solutions

Mysql – Disable trigger for just one table

Actually, if you place an if then block in every trigger, you could effectively shutdown all triggers. Here is such a code block

IF @TRIGGER_DISABLED = 0 THEN
    ...trigger body
END IF;

In the mysql environment, you could

run SET @TRIGGER_DISABLED = 1;
do your data maintenance
run SET @TRIGGER_DISABLED = 0;

So your trigger for table A should look like this:

BEGIN 
    IF @TRIGGER_DISABLED = 0 THEN
    IF (OLD.status != 1 AND NEW.status = 2) THEN 
        IF (OLD.geo_lat IS NOT NULL AND OLD.geo_long IS NOT NULL) THEN 
            DELETE FROM geo WHERE datatype IN (3,4) AND foreignid = NEW.id; 
        END IF; 
    ELSEIF (OLD.Status = 1 AND NEW.Status != 2) THEN 
        IF (NEW.geo_lat IS NOT NULL AND NEW.geo_long IS NOT NULL) THEN 
            INSERT INTO geo (datatype, foreignid, long, lat, hostid, morton, status) VALUES  (IF(NEW.groupType=1,3,4), NEW.id, NEW.geo_long, NEW.geo_lat, NEW.hostid, 0, NEW.Status); 
        END IF; 
    ELSEIF (NEW.status != 3) THEN   
        IF (OLD.geo_lat IS NOT NULL AND OLD.geo_long IS NOT NULL AND (NEW.geo_lat IS NULL OR NEW.geo_long IS NULL)) THEN 
            DELETE FROM geo WHERE datatype IN (3,4) AND foreignid = NEW.id; 
        ElSEIF ((OLD.geo_lat IS NULL OR OLD.geo_long IS NULL) AND NEW.geo_lat IS NOT NULL AND NEW.geo_long IS NOT NULL) THEN 
            INSERT INTO geo (datatype, foreignid, longitude, latitude, hostid, morton, status) VALUES  (IF(NEW.groupType=1,3,4), NEW.id, NEW.geo_long, NEW.geo_lat, NEW.hostid, 0, NEW.Status); 
        ELSEIF (OLD.geo_lat!=NEW.geo_lat OR OLD.geo_long != NEW.geo_long OR OLD.status != NEW.status) THEN 
            UPDATE geo SET lat = NEW.geo_lat, long = NEW.geo_long, status = NEW.status WHERE datatype IN (3,4) AND foreignid = NEW.id; 
        END IF; 
    END IF; 
    END IF; 
END

So your trigger for table B should look like this:

CREATE TRIGGER `usergroups_comments_insert` AFTER INSERT ON `usergroups_comment`     
    FOR EACH ROW     
    BEGIN     
        IF @TRIGGER_DISABLED = 0 THEN
            CALL sp-set-comment_count(NEW.`gid`);     
        END IF;
    END;

If you want the triggers for table A to launch but not to table B, then add the code block only to table B's trigger.

Mysql – Update Trigger in MYSQL

Unfortunately you can't use a trigger to update the table that called the trigger: from the documentation:

A stored function or trigger cannot modify a table that is already being used (for reading or writing) by the statement that invoked the function or trigger.

Could you move the admin field to a separate table which just holds the current admin id (or list of), and then you could update that with the trigger.

(of course if your admin is always id:1000, then you probably don't need the trigger to start with)

You would also need to consider whether to fire this AFTER INSERT and/or UPDATE.

An example trigger would be:

DELIMITER $$
CREATE TRIGGER update_user_admin
AFTER UPDATE ON users
FOR EACH ROW
BEGIN
    IF new.id = 1000 THEN
        UPDATE userAdmin SET id = new.id;
    END IF;
END;
$$
DELIMITER ;

Best Answer

Related Solutions

Mysql – Disable trigger for just one table

Mysql – Update Trigger in MYSQL

Related Question