0

I have two table:

// posts
+----+---------+-----------+-------------+
| id |  title  |  content  | total_votes |
+----+---------+-----------+-------------+
| 1  |  title1 |  content1 |     3       |
| 2  |  title2 |  content2 |     2       |
+----+---------+-----------+-------------+

// votes
+----+---------+-------+
| id | id_post | value |
+----+---------+-------+
| 1  |     1   |   1   |
| 2  |     1   |   1   |
| 3  |     1   |   1   |
| 4  |     2   |  -1   |
| 5  |     2   |   1   |
| 6  |     2   |   1   |
| 7  |     2   |   1   |
+----+---------+-------+

Now I need to a trigger for updating posts.total_votes. when one user gives a new vote (1 or -1), It will be a new row in votes table, SO I want to after inserting in to votes table, automatically trigger updates the number of total_votes and apply new vote. Is it possible ?

For example:

If new votes.values == 1  then posts.totla_votes++;
If new votes.values == -1 then posts.total_votes--;

EDIT:

I have two posts table (posts_A | posts_B). Also I add a new column on the votes table contained the name of tables. So I need to the trigger updates appropriate table. something like this: update new.table_name ... instead of update posts ....

// posts_A
+----+---------+-----------+-------------+
| id |  title  |  content  | total_votes |
+----+---------+-----------+-------------+
| 1  |  title1 |  content1 |     2       |
| 2  |  title2 |  content2 |    -1       |
+----+---------+-----------+-------------+

// posts_B
+----+---------+-----------+-------------+
| id |  title  |  content  | total_votes |
+----+---------+-----------+-------------+
| 1  |  title1 |  content1 |     1       |
| 2  |  title2 |  content2 |     3       |
+----+---------+-----------+-------------+

// votes
+----+---------+-------+------------+
| id | id_post | value | table_name |
+----+---------+-------+------------+
| 1  |     1   |   1   |   post_A   |
| 2  |     1   |   1   |   post_A   |
| 3  |     1   |   1   |   post_B   |
| 4  |     2   |  -1   |   post_A   |
| 5  |     2   |   1   |   post_B   |
| 6  |     2   |   1   |   post_B   |
| 7  |     2   |   1   |   post_B   |
+----+---------+-------+------------+

Here is my try, But I don't know why it does not work? :

delimiter //
create trigger total_votes_count_upd after update on votes
for each row
begin
 if (new.value == 1) then
   update new.table_name set total_votes = total_votes+1 
   where id = new.id_post;
 elseif (new.value == -1) then
   update new.table_name set total_votes = total_votes-1 
   where id = new.id_post;
 end if;
end;//

delimiter //

Actually I replaces new.table_name instead of the name of table (posts) directly. But as I said, it does not works. How can I fix it ?

Shafizadeh
  • 9,960
  • 12
  • 52
  • 89

1 Answers1

2

Yes you need to create an after insert trigger for that

delimiter //
create trigger total_votes_count after insert on votes
for each row
begin
 if (new.value == 1) then
   update posts set total_votes = total_votes+1 
   where id = new.id_post;
 elseif (new.value == -1) then
   update posts set total_votes = total_votes-1 
   where id = new.id_post;
 end if;
end;//

delimiter //

For handling the update all remains same, only you need another trigger something as

delimiter //
    create trigger total_votes_count_upd after update on votes
    for each row
    begin
     if (new.value == 1) then
       update posts set total_votes = total_votes+1 
       where id = new.id_post;
     elseif (new.value == -1) then
       update posts set total_votes = total_votes-1 
       where id = new.id_post;
     end if;
    end;//

    delimiter //

Since you have 2 post tables you will need to use that in the if condition

delimiter //
create trigger total_votes_count after insert on votes
for each row
begin
 if (new.value == 1) then
   if (new.table_name == 'post_A') then 
     update posts_A set total_votes = total_votes+1 
     where id = new.id_post;
   else
     update posts_B set total_votes = total_votes+1 
     where id = new.id_post;
   end if;
 elseif (new.value == -1) then
   if (new.table_name == 'post_A') then
      update posts_A set total_votes = total_votes-1 
      where id = new.id_post;
   else
      update posts_B set total_votes = total_votes-1 
      where id = new.id_post;
   end if ; 
 end if;
end;//

delimiter //

Do the same for update trigger.

Abhik Chakraborty
  • 44,654
  • 6
  • 52
  • 63
  • ow tnx :-) ! because I don't know trigger very well, I have three question. 1. what is `new` in `new.value` ? is it a defined (fixed) word in the trigger ? 2. can I use of `else` instead of `elseif` ? 3. what are `delimiter //` in the first and end of code ? tnx again – Shafizadeh Aug 10 '15 at 13:40
  • 1. `delimiter //` is to set a different delimiter than default `;` so that entire trigger code gets executed instead of stopping the execution at first occurrence of `;` 2. You can use `else` but this would also mean `-1,0,etc other than 1` if you are sure that the table will always have `1 and -1` then yes `else` should do the job.. 3. `new` is a keyword to refer the new value after the insert/update so here `new.id_post` will be the value of `id_post` after the insertion is done. – Abhik Chakraborty Aug 10 '15 at 13:45
  • I see, +1 vote up and accepting your solution as my answer ... :) – Shafizadeh Aug 10 '15 at 13:47
  • I think `where post.id = new.id_post;` is better, for my *mysql* version where id = new.id_post; won't work. – Shafizadeh Aug 10 '15 at 17:59
  • Please can you define in your trigger that runs `after insert or update` *(both of them)* ? I don't know its syntax .. – Shafizadeh Aug 10 '15 at 20:08
  • 1
    For update you need another trigger, just added into the answer. – Abhik Chakraborty Aug 11 '15 at 06:05
  • Thanks, and really sorry for asking again, Please can you check out my update ? (really thanks) – Shafizadeh Aug 11 '15 at 16:50
  • The reason you cant use `update new.table_name` is that this would need dynamic sql and using concat function to a string variable. You may read more about dynamic sql but those are a bit complicated. – Abhik Chakraborty Aug 12 '15 at 06:56
  • Really thanks, that was helpful. but there is a small point, In reality I have 6 tables. and I want to know, there is anything like `switch()` in trigger ? as you said, I can use of `IF_ELSE` for detecting table_name, but I think use of 6 `IF` is ugly a bit, please tell me can I use something like `switch()` instead of `IF` ? – Shafizadeh Aug 12 '15 at 07:07
  • Well there is no switch statement for mysql however there is `case when` which is similar to switch but that is mainly for select/update, but the issue is you can not set the table name on the fly using that, you will need to use dynamic sql for that, and unfortunately dynamic sql is not supported in mysql trigger :( they can only be used in store functions and procedures here is one example from my previous answer http://stackoverflow.com/questions/27542617/dynamic-table-name-at-sql-statement/27542990#27542990 – Abhik Chakraborty Aug 12 '15 at 07:11
  • aha I see, the last my question: can I use of a `@variable` in mysql and set it with `new.table_name` and then use it in `CONCAT('update', ,@table, 'set', total_votes = total_votes+1)` ? the syntax is correct ? Or can I just go with `IF` statement ? – Shafizadeh Aug 12 '15 at 07:54
  • Yes thats the correct syntax but you cant use that in trigger.. since that will need mysql prepared statement to execute the query. You have to use `if` in triggers. – Abhik Chakraborty Aug 12 '15 at 07:56
  • If you have free time, and if it is possible, and please, take a look at [here](http://stackoverflow.com/questions/32157945/how-to-check-the-number-of-user-reputation-before-storing-his-vote-via-trigger) – Shafizadeh Aug 22 '15 at 18:27
  • I also have a [trigger question](http://stackoverflow.com/questions/33806075/insert-if-exist-update-using-trigger), can you give me a solution? or at least a hint? thank you – stack Nov 19 '15 at 14:25