PostgreSQL触发器：特定列更新后

Question

PostgreSQL触发器：特定列更新后

18

我正在探索触发器，并希望创建一个在 game_saved 列的更新事件之后触发的触发器。根据我在PostgreSQL文档中所读到的，可以为列创建触发器。该列包含boolean值，因此用户可以添加或删除游戏。因此，我想让触发器函数计算某个用户在 game_saved 列中设置为 TRUE 的游戏数量。然后更新 game_collection 表中的 total_game_count。

game_collection

id - BIGSERIAL primary key
user_id - INTEGER REFERENCES users(id)
total_game_count - INTEGER

游戏信息

id - BIGSERIAL primary key
user_id - INTEGER REFERENCES users(id)
game_id - INTEGER REFERENCES games(id)
review - TEXT
game_saved - BOOLEAN

以下是我的触发器（它没有起作用，我希望找出原因）：

CREATE OR REPLACE FUNCTION total_games() 
RETURNS TRIGGER AS $$ 
BEGIN 
UPDATE game_collection 
SET total_game_count = (SELECT COUNT(CASE WHEN game_saved THEN 1 END)
                        FROM game_info WHERE game_collection.user_id = game_info.user_id)
WHERE user_id = NEW.user_id; 
RETURN NEW; 
END; 
$$ LANGUAGE plpgsql; 

CREATE TRIGGER tr_total_games 
AFTER UPDATE OF game_saved FOR EACH ROW 
EXECUTE PROCEDURE total_games();

如果我将 AFTER UPDATE OF game_saved（列）更改为 AFTER UPDATE ON game_info（表），触发器会正常工作。因此，创建一个特定于列更新的触发器存在一些问题。

在列更新时触发触发器是否是个好主意？还是我应该寻找另一种方法？

- samba

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Erwin Brandstetter · Accepted Answer

语法应该是（如手册中所述）：

CREATE TRIGGER tr_total_games 
AFTER <b>UPDATE OF game_saved ON game_info</b>
FOR EACH ROW 
EXECUTE FUNCTION total_games();

对于Postgres 10或更早版本，请使用以下命令：

...
EXECUTE PROCEDURE total_games();

看：

触发器使用过程还是函数？

但整个方法都是可疑的。使用触发器来保持聚合数据的实时性在并发写入负载下容易出现错误。而在没有并发写入负载的情况下，有更简单的解决方案：只需从当前总数中加/减1...

一个VIEW将是一个可靠的替代方案。完全删除列game_collection.total_game_count - 也许整个表game_collection，因为它似乎没有其他用途。改为创建一个VIEW：

CREATE VIEW v_game_collection AS
SELECT user_id, count(*) AS total_game_count
FROM   game_info
WHERE  game_saved
GROUP  BY user_id;

这将返回所有在game_info中至少有1行的用户，其中game_saved IS TRUE（并省略其他所有用户）。

对于非常大的表，您可能希望使用MATERIALIZED VIEW或相关解决方案来提高读取性能。这是性能、存储/缓存占用和保持最新状态之间的权衡。