CODEWITHSUNDEEP
mysqlselecttriggerscase
arkhamvm
arkhamvm

Reputation: 669

SELECT CASE statement in TRIGGER

I have a query with "SELECT CASE" statement that's working fine:

SELECT 
(CASE `t`.`is_combined` 
WHEN 0 
THEN `t`.`topic_id` 
ELSE `t`.`is_combined` 
END) AS`group_id`,
    SUM(`ctt`.`tm_download_status`) AS `is_downloaded`, 
    COUNT(`t`.`topic_id`) AS `group_topics_cnt`,
    (SUM(`ctt`.`tm_download_status`) = COUNT(`t`.`topic_id`)) AS `is_downloaded_group` 
    FROM (`catalog_topics` `t` LEFT JOIN `catalog_tracker_torrents` `ctt` ON((`ctt`.`topic_id` = `t`.`topic_id`))) 
    WHERE (`t`.`topic_id` != 0) 
    GROUP BY (`group_id`)

So, i want to create a similar trigger to update "cross" table:

DELIMITER $$ 
CREATE TRIGGER `tdg_ins_by_topics` AFTER INSERT ON `catalog_topics` FOR EACH ROW 
BEGIN
REPLACE INTO catalog_topics_downloaded_groups(
      SELECT (
              CASE `t`.`is_combined`
              WHEN 0
              THEN `t`.`topic_id`
              ELSE `t`.`is_combined`
              END
     ) AS `group_id` , 
SUM( `ctt`.`tm_download_status` ) AS `is_downloaded` , 
COUNT( `t`.`topic_id` ) AS `group_topics_cnt` , (
SUM( `ctt`.`tm_download_status` ) = COUNT( `t`.`topic_id` ) ) AS `is_downloaded_group`
FROM `catalog_topics` `t`
LEFT JOIN `catalog_tracker_torrents` `ctt` ON `ctt`.`topic_id` = `t`.`topic_id`
WHERE `t`.`topic_id`
IN (
NEW.`topic_id`
)
GROUP BY `group_id`
)
END ;
$$

But getting an error message:

"#"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 'END' at line 14

It's look like that MySQL doesn't understand difference between CASE in TRIGGER statement and CASE in SELECT statement. So, how i can fix this?

Thanks for the answers.

Upvotes: 1

Views: 5389

Answers (1)

Kaii
Kaii

Reputation: 20540

I think you need to "end" your REPLACE statement with ; like you have to end all statements inside a TRIGGER or PROCEDURE/FUNCTION with a delimeter.

That is why you change the DELIMETER to $$ .. so you can use ; to store the mysql default delimeter inside of the trigger code. (and end the create trigger statement with the changed $$ delimeter)

DELIMITER $$ 
CREATE TRIGGER `tdg_ins_by_topics` AFTER INSERT ON `catalog_topics` FOR EACH ROW 
BEGIN
    REPLACE INTO catalog_topics_downloaded_groups(
          SELECT ( CASE `t`.`is_combined`
                   WHEN 0
                   THEN `t`.`topic_id`
                   ELSE `t`.`is_combined`
                   END
                 ) AS `group_id`, 
                 SUM(`ctt`.`tm_download_status`) AS `is_downloaded`, 
                 COUNT( `t`.`topic_id` ) AS `group_topics_cnt` , 
                 (
                 SUM( `ctt`.`tm_download_status` ) = COUNT( `t`.`topic_id` ) ) AS `is_downloaded_group`
          FROM `catalog_topics` `t`
          LEFT JOIN `catalog_tracker_torrents` `ctt` ON `ctt`.`topic_id` = `t`.`topic_id`
          WHERE `t`.`topic_id` IN ( NEW.`topic_id` )
          GROUP BY `group_id`
    );
END;
$$
DELIMETER ;

Upvotes: 2

Related Questions