creating MySQL Trigger to insert into second table

Question

I'm trying to setup a trigger to insert a sum into a second table after insert of the first table.

The tables are:

• First Table Name: likes

• Fields: feedid

• Second Table Name: like_count

• Fields: total, feedid

After insert on likes table, I need to check if the feedid exists in table like_count. If not just insert it with total of 1. If it does exists, I need to update like_count.total to increment by one where likes.feedid = like_count.feedid

Answer 1

Of course you have to change the databasename and adept the columns and the insert statement

DELIMITER //
CREATE TRIGGER `databasename`.`update_feed_count`
AFTER INSERT ON `likes` FOR EACH ROW
BEGIN
    DECLARE @feedid  VARCHAR(20);
    SELECT feedid INTO @feedid FROM like_count WHERE feedid = NEW.feedid;
    IF @feedid IS NULL OR @feedid = '' THEN
        INSERT INTO like_count (total, feedid) VAKUES (1,NEW.feedid);
    ELSE
        UPDATE like_count lc 
            WHERE `total` = `total` + 1 
            AND NEW.feedid = vc.feedid;
    END IF;
END;
DELIMITER ;

Answer 2

You can use MySQL INSERT ... ON DUPLICATE KEY syntax to simplify the logic of the trigger.

For this to work, feedid must be a unique column in table likes_count (either it is the primary key of the column, or you need to create a UNIQUE constraint on it). Then:

DELIMITER //
CREATE TRIGGER update_likes_count
AFTER INSERT ON likes FOR EACH ROW
BEGIN
    INSERT INTO likes_count (feed_id, total) VALUES (NEW.feedid, 1)
    ON DUPLICATE KEY UPDATE total = total + 1;
END;
//
DELIMITER;