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.
Best Answer
There is great reason why you should never call stored procedures from within triggers.
Triggers are, by nature, stored procedures. Their actions are virtually hard to roll back. Even if all underlying tables are InnoDB, you will experience a proportional volume of shared row locks and annoying intermittency from exclusive row locks. Such would be the case if triggers were manipulating tables with INSERTs and UPDATEs being stagnated to perform heavy duty MVCC inside each call to a trigger.
Don't forget that Triggers require overhead. In fact, According to MySQL Stored Procedure Programming, page 256 under the head "Trigger Overhead" says the following:
An expanded explanation of trigger overhead is given on pages 529-531. The concluding point from that section states the following:
I explained other nasty aspects of Triggers in an earlier post.
SUMMARY
I would strongly recommend not calling any stored procedures from a Trigger, even if MySQL allows it. You should wlays check out the current restrictions for MySQL 5.5.