Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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
share|improve this question
add comment

1 Answer

up vote 3 down vote accepted

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 ;
share|improve this answer
    
I think DUPLICATE KEY UPDATE is exactly what I would need, as I get an error about having a key already existing. Still lots to learn of triggers. Are triggers more efficient than doing the manual query in your opinion? –  kabuto178 Feb 19 '13 at 15:20
    
I'm not particularly fond of triggers. They are quite difficult to debug and maintain. –  ypercube Feb 19 '13 at 15:22
    
I am beginning to realize how difficult they are to debug. Your suggestion works, this might be useful to other new users of triggers as well. Thanks again –  kabuto178 Feb 19 '13 at 15:23
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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