CODEWITHSUNDEEP
sqldatabasepostgresql
karrax
karrax

Reputation: 15

PostgreSQL Create Trigger which runs a function on every insert or update of a table

I have defined two tables, scores and analyzed_avg_score, in my postgres database. I also have a function which i declaired like that:

CREATE FUNCTION updateAvgScore() RETURNS void AS $$
    INSERT into analyzed_avg_score
        (SELECT 
            user,
            avg(score_value)
        FROM
            scores
        group by user) on conflict do nothing;
$$ LANGUAGE SQL;

Now, I want to have a trigger or something similar that runs this function every time I insert or update something in score. I don't have a lot of experience with SQL, yet. So, does anyone have an idea how the trigger should look like?

Upvotes: 0

Views: 959

Answers (1)

SELA
SELA

Reputation: 6803

CREATE TRIGGER SCORE_INSERT AFTER INSERT ON SCORE
FOR EACH ROW EXECUTE PROCEDURE updateAvgScore();


/*Have it return a trigger like this */

CREATE OR REPLACE FUNCTION updateAvgScore() RETURNS TRIGGER AS $example_table$
   BEGIN
      /*YOUR lOGIC HERE*/
   END;
$example_table$ LANGUAGE plpgsql;

Upvotes: 2

Related Questions