I have created a stored procedure in mysql using the following syntax.
DROP PROCEDURE IF EXISTS `sp-set_comment_count`; DELIMITER $$ CREATE PROCEDURE `sp_set-comment_count` (IN _id INT) BEGIN -- AC - AllCount DECLARE AC INT DEFAULT 0; SELECT COUNT(*) AS ac INTO AC FROM usergroups AS ug LEFT JOIN usergroup_comments AS ugm ON ugm.`gid` = ug.`id` LEFT JOIN mediagallery AS dm ON ugm.mid = dm.`id` WHERE dm.`status` NOT IN (200, 201, 202, 203, 204, 205) AND ug.`id` = _id; UPDATE usergroups SET allCount = AC, WHERE usergroups.`id` = _id; END $$ DELIMITER ;
FYI I've greatly simplified the stored procedure but I do know it works without any issues.
What I'd like to be able to do is set up a trigger from usergroup_comments that works like this.
DROP TRIGGER IF EXISTS `usergroups_comments_insert` CREATE TRIGGER `usergroups_comments_insert` AFTER INSERT ON `usergroups_comment` FOR EACH ROW BEGIN CALL sp-set-comment_count(NEW.`gid`); END;
But for some reason every time I do mysql throws an error at me that's less than helpful stating that there's a syntax error on line 4.
I've combed through the mysql documentation and found some information on restrictions of triggers but found it to be fairly convoluted.
Any ideas would be helpful.