CODEWITHSUNDEEP
mysql
Nick Nelson
Nick Nelson

Reputation: 1293

Trigger to update row in another table

This is a select statement that calculates the average rating of a song in my database

SELECT *, (tab.rating_sum/tab.rating_count) as rating_average FROM
(SELECT song_id, COUNT(rating) rating_count, SUM(rating) rating_sum FROM ratings
GROUP BY song_id) tab

INNER JOIN SONGS WHERE SONGS.id = tab.song_id

I need to insert the column rating_average from the table RATINGS into the table SONGS when a new rating is inserted in RATINGS. I also need to make sure it goes into the proper song_id column. How can I do this with a trigger? I have been ripping my hair out.

This is the best that I can come up with, but I have no idea what I am doing with triggers:

CREATE TRIGGER rating_trig ON ratings FOR INSERT AS
BEGIN
UPDATE SONGS SET
rating = SELECT (tab.rating_sum/tab.rating_count) as rating_average FROM
(SELECT song_id, COUNT(rating) rating_count, SUM(rating) rating_sum FROM ratings
GROUP BY song_id) tab

INNER JOIN SONGS WHERE SONGS.id = tab.song_id
END

TABLE LAYOUTS:

RATINGS | song_id, rating, username SONGS | id, rating, song_name

Upvotes: 4

Views: 6621

Answers (1)

Marc Alff
Marc Alff

Reputation: 8395

Assuming a table structure like this one:

use test;

create table song(
  song_id integer,
  rating_avg double,
  rating_sum integer,
  rating_count integer);

create table rating(
  song_id integer,
  user_id integer,
  rating integer);

Define the following triggers:

delimiter $$

create trigger bi_song before insert on test.song
for each row
begin
  set NEW.rating_sum = 0;
  set NEW.rating_count = 0;
  set NEW.rating_avg = NULL;
end
$$

create trigger ai_rating after insert on test.rating
for each row
begin
  update song set
    rating_sum = rating_sum + NEW.rating,
    rating_count = rating_count + 1,
    rating_avg = rating_sum / rating_count
    where song_id = NEW.song_id;
end
$$
delimiter ;

And it should work like this:

mysql> insert into song(song_id) values (1);
Query OK, 1 row affected (0.06 sec)

mysql> select * from song;
+---------+------------+------------+--------------+
| song_id | rating_avg | rating_sum | rating_count |
+---------+------------+------------+--------------+
|       1 |       NULL |          0 |            0 |
+---------+------------+------------+--------------+
1 row in set (0.00 sec)

mysql> insert into rating(song_id, user_id, rating) values (1, 1000, 5);
Query OK, 1 row affected (0.05 sec)

mysql> select * from song;
+---------+------------+------------+--------------+
| song_id | rating_avg | rating_sum | rating_count |
+---------+------------+------------+--------------+
|       1 |          5 |          5 |            1 |
+---------+------------+------------+--------------+
1 row in set (0.00 sec)

mysql> insert into rating(song_id, user_id, rating) values (1, 1001, 7);
Query OK, 1 row affected (0.05 sec)

mysql> select * from song;
+---------+------------+------------+--------------+
| song_id | rating_avg | rating_sum | rating_count |
+---------+------------+------------+--------------+
|       1 |          6 |         12 |            2 |
+---------+------------+------------+--------------+
1 row in set (0.00 sec)

This is very simplified, just to illustrate how to work with triggers. In a real application, you would have more columns, indexes, etc.

Upvotes: 6

Related Questions