CODEWITHSUNDEEP
sqlstored-procedurestriggersmariadb
chindit
chindit

Reputation: 1009

SQL: TRIGGER and PROCEDURE

I have a table «blog» with a list of items. I would like to add a column with the number of comments for every item. I would also this number to be automatically updated.

So, I've created a procedure:

DELIMITER |
CREATE PROCEDURE Procedure_comments()
BEGIN
UPDATE blog SET nb_comment = (SELECT COUNT(Comment.id) 
                              FROM Comment 
                              WHERE Comment.blog_id = blog.id);
END|

And I've tried to add a trigger which, in my mind would «launch» the procedure when inserting/deleting a comment:

CREATE TRIGGER trigger_nb_comments 
AFTER INSERT ON Comment 
FOR EACH ROW EXECUTE Procedure_comments()|

But… I got an error:

ERROR 1336 (0A000): Dynamic SQL is not allowed in stored function or trigger

So… How can I proceed to automatically update the number of comments on my main table «blog»?

Thanks a lot for your help.

Upvotes: 1

Views: 84

Answers (1)

Lukasz Szozda
Lukasz Szozda

Reputation: 176064

Trigger you proposed will recalculate/update nb_comment for every row in blog table every time when user insert comment and will not handle comments deletion.

Instead of trigger and I suggest using simple view:

CREATE VIEW vw_blog
AS
SELECT b.id, b.title, b.body,
       COUNT(*)  AS nb_comment
FROM blog b
JOIN `Comment` c
  ON b.id = c.blog_id
GROUP BY b.id, b.title, b.body;

SqlFiddleDemo

Output:

╔═════╦════════════════════╦═══════════════════════════════════╦════════════╗
║ id  ║       title        ║               body                ║ nb_comment ║
╠═════╬════════════════════╬═══════════════════════════════════╬════════════╣
║  1  ║ SQL Server         ║ SQL Server...                     ║          3 ║
║  2  ║ MySQL performance  ║ To improve performance invest...  ║          1 ║
╚═════╩════════════════════╩═══════════════════════════════════╩════════════╝

Upvotes: 0

Related Questions