Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
So it turns out the problem with the above stored procedure being called was the fact that it had a hyphen in it's name. Changing the stored procedure name to sp_set_comment_count resolved the issue. –  Mark D Jan 16 '12 at 16:32
    
Awesome Mark! You can add your own answer and accepting it. –  Derek Downey Jan 16 '12 at 16:39
add comment

4 Answers 4

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:

It is important to remember that, by necessity, triggers add overhead to the DML statement to which they apply. the actual amount of overhead will depend upon the nature of the trigger, but --- as all MySQL triggers execute FOR EACH ROW --- the overhead can rapidly accumulate for statements that process large numbers of rows. You should therefore avoid placing any expensive SQL statements or procedural code in triggers.

An expanded explanation of trigger overhead is given on pages 529-531. The concluding point from that section states the following:

The lesson here is this: since the trigger code will execute once for every row affected by a DML statement, the trigger can easily become the most significant factor in DML performance. Code inside the trigger body needs to be as lightweight as possible and -- in particular -- any SQL statements in the trigger should be supported by indexes whenever possible.

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.

share|improve this answer
    
Interesting, thanks for the heads up. The lack of transactional queries in our environment mitigates the transaction issue. However I can appreciate the idea of accumulating overhead. I guess I'll watch the db for a while to see what the result of this change is. –  Mark D Jan 13 '12 at 19:50
    
I don't think it's accurate to conflate triggers with stored procedures. At a minimum, it's valid to start and commit a transaction in a stored procedure. MySQL complains if you try to do the same in a trigger. Which is silly, because having a trigger that needs to transactionally update one or more tables in response to some change is an entirely valid use-case which should be supported in a straightforward manner. –  aroth Jan 24 at 5:44
add comment
up vote 4 down vote accepted

So it turns out this is the issue that plagued me for a few hours believe it or not.

I can easily define a procedure called, sp_set-comment_count. However when calling said procedure, it doesn't work the same way.

CALL sp_set-comment_count (I can only assume this is because the server interprets the - as a minus).

I have since changed the stored procedure name to only use underscores and it seems to have resolved everything.

share|improve this answer
add comment

If it says about syntax error, the most likely you forgot to change delimiter (as you did for stored procedure). So you need

DELIMITER $$
CREATE TRIGGER `usergroups_comments_insert` AFTER INSERT ON `usergroups_comment`
FOR EACH ROW
BEGIN
   CALL sp_set_count(NEW.`gid`);
END;
$$
share|improve this answer
    
Thank you this actually got me thinking along the right track. In fact my sp was called sp-set_comment_count. When called by a trigger it appears that the issue was that when call the SP from the trigger the - kept throwing the error. –  Mark D Jan 13 '12 at 19:25
add comment

Looks like the comma after AC is a syntax error:

UPDATE usergroups
   SET allCount = AC,
 WHERE ........
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.