如何为增加/减少1个数字到总投票数创建更新触发器 [英] how to create a update trigger for Increase/Decrease 1 number to total votes number

问题描述

我有两个表:

// 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   |
+----+---------+-------+

现在，我需要一个触发器来更新posts.total_votes.当一个用户给出新的投票(1或-1)时，它将是votes表中的新行，所以我想在插入到votes表中后自动触发更新total_votes的数量并应用新的投票.有可能吗?

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 ?

例如:

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

---

我有两个帖子表(posts_A | posts_B).另外，我在投票表上添加了一个新列，其中包含表的名称.因此我需要为触发器更新适当的表.像这样:update new.table_name ...而不是update posts ....

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 //

实际上，我直接替换了new.table_name而不是表的名称(posts).但是正如我所说，这是行不通的.我该如何解决?

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 ?

推荐答案

是的，您需要为此创建一个after insert trigger

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 //

由于您有2个帖子表，因此您需要在if条件下使用它

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.

这篇关于如何为增加/减少1个数字到总投票数创建更新触发器的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！