0

I have 1 table with an id (auto increment), username, email, icon, comment, rating, date (on update current_timestamp, left empty as now() does not function so must pre-date) and counts.

What I want to do is set up a trigger for every insert (count + 1), for every delete (count -1). The end result would be used for pagination so if I have 10 comments 5 to a page (10/2) --> #pagination would have two pages. Anyway I would like to welcome myself to responsive web design using AJAX jquery, JSON etc;

My SQL code is shown below for an insert update on count but the count is not incrementing, how would I delete also!

DELIMITER $$
CREATE
TRIGGER `CommentsTrigger` BEFORE INSERT ON Comments
FOR EACH ROW
BEGIN
UPDATE Comments
SET counts = (Select COUNT(*) from Comments)+ 1;
END $$
DELIMITER;

version 5.5!

I think the value needs to be saved as an integer but that is a guess!

Alex
  • 71
  • 10
  • 1
    So if I'm not seeing something wrong, every single row in your `Comments` table would have a column `counts`, correct? Are you sure you are not getting any errors while creating this trigger? You shouldn't be able to update the same table that trigger refers to. You should have another table, called `statistics` or similar, which you'd update from the trigger. – Mjh Jun 14 '16 at 14:49
  • so u cant update the same table? what about saving counts as an int that might be the second error – Alex Jun 14 '16 at 14:50
  • 1
    Saving counts is fine, the problem is *where* you save it. When you use triggers, you should never issue queries that modify contents of the table it refers to - that can lead to an infinite loop and consequently, you are not allowed to create such a trigger. Create another table (you can call it `statistics`) and you can save counts in there. I'd create 2 columns, one for the table whose count I'm saving and another column for the actual count of rows. – Mjh Jun 14 '16 at 14:55
  • http://www.kevinswebsites.me/commentform.html this is beginners responsive webdesign, worth a smile or 2 – Alex Jun 14 '16 at 14:55
  • why would I need a count for table + other table if get this right? – Alex Jun 14 '16 at 14:58
  • I can explain everything, but what bothers me is - do you understand why your trigger doesn't work and what I'm suggesting by using another table? – Mjh Jun 14 '16 at 15:04
  • It could cause a clash if 2 inserts happen at same time? – Alex Jun 14 '16 at 15:06
  • 1
    No, not really. I'd suggest reading about restrictions on triggers before proceeding further. It's actually a good technique to store counts somewhere, but you need to read a bit on what limitations of triggers are. – Mjh Jun 14 '16 at 15:09
  • Each table only has 1 trigger per timing so if 2 inserts happened, 2 triggers could not occur, this might be due to limited resources of the admin – Alex Jun 14 '16 at 15:11
  • No, that's not true, how did you conclude that? – Mjh Jun 14 '16 at 15:28

1 Answers1

2

If you want to keep all record counts it's better to save it in another table and change the update command to increase and decrease a field.

Your code, if works, is very slow because of using count(*).

Use something like this:

DELIMITER $$
CREATE
TRIGGER `CommentsTrigger` AFTER INSERT ON Comments
FOR EACH ROW
BEGIN
UPDATE tbl_stat
SET counts = counts+1;
END $$
DELIMITER ;


DELIMITER $$
CREATE
TRIGGER `CommentsTrigger` AFTER DELETE ON Comments
FOR EACH ROW
BEGIN
UPDATE tbl_stat
SET counts = counts-1;
END $$
DELIMITER ;

You must create tbl_stat(id int, counts int).

And create delete triger like above except "counts = counts-1".

For only one time insert your comments table rows count to tbl_stat.

Edit : I changed the before insert to after insert.

Edit : I added delete trigger.

Mostafa Vatanpour
  • 1,328
  • 13
  • 18
  • The triggers show an inital error with the delimiters and when I insert a row the count does not change, im looking for typos – Alex Jun 14 '16 at 15:49
  • You can refer to https://dev.mysql.com/doc/refman/5.5/en/create-trigger.html and https://dev.mysql.com/doc/refman/5.5/en/trigger-syntax.html – Mostafa Vatanpour Jun 14 '16 at 16:00
  • its working now, what I had to do was insert a row and then start counting, obviously matching it up to the number of comments. Another thing is that sql 5.5 throws errors on line 1 when you use delimiter. That is what made it awkward. – Alex Jun 14 '16 at 16:15
  • 1
    I corrected the delimiter error by placing a space between "delimiter" and ";" character. – Mostafa Vatanpour Jun 14 '16 at 19:25