20

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.

1
  • 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, 2012 at 16:32

4 Answers 4

25

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.

3
  • 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, 2012 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, 2014 at 5:44
  • 2
    So I have this trigger, that is really big. It performs several calculations on my table, both upon insert and update. Triggers in Mysql can really become painful when they are complex. It would be much easier to break down the trigger into procedures.
    – Lamar
    Oct 2, 2017 at 12:02
9

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.

1
  • 3
    Late to the party but: you have created your SP using a quoted identifier, which allowed special characters in its name, so you should reference it similarly elsewhere: CALL `sp-set-comment_count`(NEW.`gid`);
    – mustaccio
    May 24, 2019 at 22:10
5

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;
$$
1
  • 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, 2012 at 19:25
1

Looks like the comma after AC is a syntax error:

UPDATE usergroups
   SET allCount = AC,
 WHERE ........
1
  • Valid point, but not the actual cause of the error in this case I simply trimmed some extra sets from that query and forgot to remove the ,
    – Mark D
    May 27, 2019 at 16:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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