我有两个表:
// 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
的数量并应用新的投票。这可能吗?
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 ...
.
// 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 |
+----+---------+-------+------------+
这是我的尝试,但我不知道为什么它不工作?:
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
)。但就像我说的,这行不通。
是的,你需要为它创建一个after insert trigger
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 //
对于处理更新所有保持不变,只是您需要另一个触发器作为
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 //
因为你有两个post表,所以你需要在if条件
中使用它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 //
对update trigger做同样的操作