尝试创建触发器时出现Mysql错误＃1064

I am trying to create a MySQL trigger but don't know why error #1064 occurs. Here is the full error message:

我试图创建一个MySQL触发器,但不知道为什么错误#1064发生。这是完整的错误消息:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DECLARE popularity_cur CURSOR FOR select (sum(tbl_reviews.vote_value)/count(tbl_' at line 22

#1064 - 您的SQL语法有错误;查看与您的MySQL服务器版本对应的手册,以便在'DECLARE popular_cur CURSOR FOR'附近使用正确的语法(sum(tbl_reviews.vote_value)/ count(第22行的tbl_')

Line #22 is close cur;

22号线很近;

and here is what I've done so far:

这是我到目前为止所做的:

delimiter //
CREATE TRIGGER business_popularity_trigger_after_review_insert 
AFTER INSERT ON tbl_reviews FOR EACH ROW
BEGIN
DECLARE data_finished INTEGER DEFAULT 0;
DECLARE businessId, reviewId, dealId, businessServiceId INT DEFAULT 0; 
DECLARE businessPopularity INTEGER DEFAULT 0;
DECLARE popularity_finished INTEGER DEFAULT 0;

DECLARE cur CURSOR FOR select tbl_reviews.review_id, tbl_reviews.deal_id, deals.deal_id, deals.business_service_id, business_services.business_service_id, business_services.business_id from tbl_reviews INNER JOIN deals ON deals.deal_id = new.deal_id INNER JOIN business_services ON business_services.business_service_id = deals.business_service_id  
where tbl_reviews.review_id = new.review_id 
group by business_services.business_id;

DECLARE CONTINUE HANDLER FOR NOT FOUND SET data_finished = 1;
OPEN cur;
    get_data: LOOP
        fetch cur into reviewId, dealId, businessServiceId, businessId;
        IF data_finished = 1 THEN 
            LEAVE get_data;
        END IF;
   END LOOP get_data;
close cur;
DECLARE popularity_cur CURSOR FOR select (sum(tbl_reviews.vote_value)/count(tbl_reviews.vote_value)) from tbl_reviews INNER JOIN business_services ON business_services.business_id = businessId INNER JOIN deals ON deals.business_service_id = business_services.business_service_id where deals.deal_is_deleted = 'N' and tbl_reviews.review_is_deleted = 'N' and tbl_reviews.review_status = 'Y';
set data_finished = 0;
IF businessId > 0 THEN
    OPEN popularity_cur;
        get_popularity: LOOP
            fetch popularity_cur into businessPopularity;
            IF data_finished = 1 THEN 
                LEAVE get_popularity;
            END IF;
       END LOOP get_popularity;
    close popularity_cur;
Update business 
set business.business_popularity = businessPopularity  
where business.business_id = businessId;
END IF;
delimiter;

Please help me. Thanks

请帮我。谢谢

1 个解决方案

#1

You should use correct delimiter // at the end and close the BEGIN ... END construction. So change last lines to:

您应该在末尾使用正确的分隔符//并关闭BEGIN ... END构造。所以将最后一行更改为:

END IF;
END//
DELIMITER;

UPDATE Since you use aggregating functions SUM, COUNT you need GROUP BY statement. So try this way:

更新由于您使用聚合函数SUM,COUNT,您需要GROUP BY语句。所以试试这样:

DECLARE popularity_cur CURSOR FOR 
  SELECT (sum(tbl_reviews.vote_value)/count(tbl_reviews.vote_value)) 
  FROM tbl_reviews 
  INNER JOIN business_services 
  ON business_services.business_id = tbl_reviews.businessId 
  INNER JOIN deals 
  ON deals.business_service_id = business_services.business_service_id 
    AND deals.deal_is_deleted = 'N' 
  WHERE tbl_reviews.review_is_deleted = 'N' 
    AND tbl_reviews.review_status = 'Y'
  GROUP BY tbl_reviews.businessId;

I did some optimization and you had wrong condition business_services.business_id = businessId so I did change to business_services.business_id = tbl_reviews.businessId.

我做了一些优化,你的条件错误了business_services.business_id = businessId所以我做了更改为business_services.business_id = tbl_reviews.businessId。

NOTE Probably that wasn't a mistake and you need that business_services.business_id = businessId condition. But even if you need that you should use one condition for JOIN and another one to filter. so it could be like:

注意可能这不是一个错误,您需要business_services.business_id = businessId条件。但即使您需要,也应该使用一个条件进行JOIN,另一个条件进行过滤。所以它可能像:

INNER JOIN business_services 
  ON business_services.business_id = tbl_reviews.business_id
    AND business_services.business_id = businessId

UPDATE 2 According to the screenshot with your database schema your query could be like:

更新2根据您的数据库架构的屏幕截图,您的查询可能是:

DECLARE popularity_cur CURSOR FOR 
  SELECT (sum(tbl_reviews.vote_value)/count(tbl_reviews.vote_value)) 
  FROM tbl_reviews 
  INNER JOIN deals 
  ON  tbl_reviews.deal_id = deals.deal_id 
    AND deals.deal_is_deleted = 'N' 
  INNER JOIN business_services 
  ON business_services.business_service_id  = deals.business_service_id 
  WHERE tbl_reviews.review_is_deleted = 'N' 
    AND tbl_reviews.review_status = 'Y'
  GROUP BY deals.business_service_id;

#1