MYSQL Triggers syntax

Question

I need some help with my trigger statement, as I would like to only insert the new row if a column value does not already exist, or if it does exist update a corresponding column. Currently I have a trigger to insert the new row data then another trigger to update it. Any help appreciated.

TRIGGER `status_insert` BEFORE INSERT ON  `member_status_likes` 
FOR EACH
ROW INSERT INTO member_status_like_count( status_id, status_likes ) 
VALUES (
NEW.status_id, 1
)



 TRIGGER `status_like_increment` AFTER INSERT ON  `member_status_likes` 
FOR EACH
ROW UPDATE member_status_like_count SET status_likes = ( status_likes +1 ) WHERE status_id = NEW.status_id

Answer 1

I guess you want to either insert a new row with the counter status_likes = 1 or incresase it by 1 when a row with status_id already exists. If there is a UNIQUE constraint on member_status_like_count (status_id), you can use INSERT ... ON DUPLICATE KEY UPDATE:

CREATE TRIGGER member_status_likes__AFTER_INSERT__UPSERT__like_count
AFTER INSERT ON member_status_likes 
FOR EACH ROW 
  INSERT INTO member_status_like_count 
      (status_id, status_likes) 
  VALUES
      (NEW.status_id, 1)
  ON DUPLICATE KEY UPDATE
      status_likes = status_likes + 1 ;