File indexing completed on 2024-04-21 06:00:37
0001 DROP TRIGGER IF EXISTS `comment_update`; 0002 0003 DELIMITER $$ 0004 0005 CREATE DEFINER = CURRENT_USER TRIGGER `comment_update` 0006 BEFORE UPDATE 0007 ON `comments` 0008 FOR EACH ROW 0009 BEGIN 0010 DECLARE ratingID INT DEFAULT NULL; 0011 IF `NEW`.`comment_active` = 0 AND `OLD`.`comment_active` = 1 THEN 0012 0013 UPDATE `project` `p` 0014 SET `p`.`count_comments` = (`p`.`count_comments` - 1) 0015 WHERE `p`.`project_id` = `NEW`.`comment_target_id`; 0016 0017 SET `NEW`.`comment_deleted_at` = NOW(); 0018 0019 set ratingID:= (select rating_id from project_rating where comment_id = `OLD`.`comment_id`); 0020 IF(ratingID IS NOT NULL and ratingID>0) THEN 0021 update project_rating set rating_active = 0 where rating_id = ratingID; 0022 END IF; 0023 0024 END IF; 0025 0026 IF `NEW`.`comment_active` = 1 AND `OLD`.`comment_active` = 0 THEN 0027 0028 UPDATE `project` `p` 0029 SET `p`.`count_comments` = (`p`.`count_comments` + 1) 0030 WHERE `p`.`project_id` = `NEW`.`comment_target_id`; 0031 0032 SET `NEW`.`comment_deleted_at` = NULL; 0033 0034 set ratingID:= (select rating_id from project_rating where comment_id = `OLD`.`comment_id`); 0035 IF(ratingID IS NOT NULL and ratingID>0) THEN 0036 update project_rating set rating_active = 1 where rating_id = ratingID; 0037 END IF; 0038 0039 END IF; 0040 0041 END$$ 0042 DELIMITER ;