CODEWITHSUNDEEP
postgresqltriggerssql-update
ErichFelzinger
ErichFelzinger

Reputation: 17

Update column value with trigger postgresql/ fetch value from insert query

Whenever user rates a movie such trigger should be called and update the rating of a rated movie. How do I fetch value of movie_id from query insert into ratings values (movie_id, /*etc*/)?

create trigger new_rating after insert on ratings
execute procedure update_movie_rating();

create or replace function update_movie_rating()
returns trigger 
as $new_rating$
begin 
update movies 
set averagerating =
select avg(r.rating)
from ratings r
where r.movie_id = /*movieid fetched from insert query*/
return new;
end;
$new_rating$ language plpgsql;

Upvotes: 0

Views: 233

Answers (1)

user_0
user_0

Reputation: 3363

Assuming field in table "ratings"is called movie_id:

where r.movie_id = NEW.movie_id

You can find a detailed explanation here: https://www.postgresql.org/docs/current/plpgsql-trigger.html

Anyway, you are interested in this specific part:

When a PL/pgSQL function is called as a trigger, several special variables are created automatically in the top-level block. They are:

NEW:

Data type RECORD; variable holding the new database row for INSERT/UPDATE operations in row-level triggers. This variable is null in statement-level triggers and for DELETE operations.

Upvotes: 1

Related Questions