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.
http://dev.mysql.com/doc/refman/5.1/en/stored-program-restrictions.html
Any ideas would be helpful.