问题描述
我有两个表:
// 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个数字到总投票数创建更新触发器的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！