Postgresql触发器计算总分 [英] Postgresql trigger to calculate total score

问题描述

我正在尝试学习如何在postgresql中创建触发器。我有一张桌子

I am trying to learn how to create a trigger in postgresql. I have a table

Thread_user-表名
thread_id
user_id
点

Thread_user - table name thread_id user_id points

线程-表名
thread_id
total_points

Thread - table name thread_id total_points

我想更新任何thread_user行以更新线程表中的总点。我基本上需要从thread_user中选择*，其中thread_id =插入项的thread_id，然后添加点，然后更新线程表中的thread_points。我相信这是在触发器中完成的，但也许存储过程会更好。

I want to on update of any thread_user row to update the total points in the thread table. I need to basically select * from thread_user where thread_id = thread_id of inserted item and then add the points then update thread_points in the threads table. I believe this is done in triggers but maybe a stored procedure would be better.

推荐答案

第一步是创建一个计算函数

First step is to make a function which calculates the sum of points and updates a row in the calculated_points table.

此后，您将创建一个触发器，该触发器将在user_points表中插入一行后调用。 / p>

Thereafter you'll ahve to create a trigger which is called upon inserting a row in the user_points table.

DROP TABLE IF EXISTS user_points CASCADE;
CREATE TABLE user_points (
    id          SERIAL PRIMARY KEY,
    user_id     INT NOT NULL,
    points      INT NOT NULL
);

DROP TABLE IF EXISTS calculated_points CASCADE;
CREATE TABLE calculated_points (
    id          SERIAL PRIMARY KEY,
    user_id     INT  NOT NULL UNIQUE,
    points      INT NOT NULL

);

INSERT INTO calculated_points (user_id, points)
    VALUES
        (1, 0),
        (2, 0);

CREATE OR REPLACE FUNCTION calculate_total_points() 
RETURNS trigger AS $calculate_total_points$
BEGIN
    UPDATE calculated_points 
        SET points = (SELECT SUM(points)
                         FROM user_points
                         WHERE user_id = NEW.user_id)
         WHERE user_id = NEW.user_id;

    RETURN NEW;
END;
$calculate_total_points$ LANGUAGE plpgsql;

CREATE TRIGGER points_added
  AFTER INSERT
  ON user_points
  FOR EACH ROW
  EXECUTE PROCEDURE calculate_total_points();

这篇关于Postgresql触发器计算总分的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！