21

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.

Improve this question
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. Commented Jan 16, 2012 at 16:32

5 Answers 5

Reset to default
26

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.

Improve this answer
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. Commented 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. Commented 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. Commented 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.

Improve this answer
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`); Commented 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; $$
Improve this answer
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. Commented Jan 13, 2012 at 19:25
1

Looks like the comma after AC is a syntax error:

UPDATE usergroups SET allCount = AC, WHERE ........
Improve this answer
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 , Commented May 27, 2019 at 16:05
0

Different names for defining and calling the procedure would cause a problem.

Has anyone noticed that

sp-set-comment_count

and

sp_set-comment_count

are different? After the sp, one has an underscore and the other has a hyphen. In addition to the quoted identifier mentioned by @mustaccio

Improve this answer

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.