natedawg12 natedawg12 - 7 months ago 18
SQL Question

Update table when another table is updated

I have 2 tables TESTUserInfo and BlockRanking.

TestUserInfo is a bunch of users info and totals for their specific user. Each user also has a zone associated with their user.

In BlockRanking I combine all of the users with each zone and sum all of the total points for that zone.

I have this query to insert values into table BlockRanking from TESTUserInfo and it works.

INSERT INTO BlockRanking (zone, total)
SELECT zone, SUM(total)
FROM TESTUserInfo
GROUP BY zone
ORDER BY SUM(total) DESC


Now I am trying to do something similar to this but I want to update BlockRanking when TESTUserInfo is update. I specifically want to update the zones and the total of those zones in BlockRanking after they are grouped by.

I would like this to be a trigger so when TESTUserInfo is updated BlockRanking will also be updated, or on a time basis. If These are not possible just a query will work too.

Answer

You can use triggers to run tasks when certain queries are run on a table. A trigger runs once for each inserted row. Instead of aggregating, we can keep track of the totals that are added into the TESTUserInfo table as they arrive.

CREATE TRIGGER update_totals AFTER INSERT ON TESTUserInfo
  FOR EACH ROW
  BEGIN
    INSERT INTO BlockRanking
       (zone, total)
       VALUES 
       (NEW.zone, NEW.total)
    ON DUPLICATE KEY UPDATE
        total = total + VALUES('total');
  END;

Note that zone would have to be a unique index in BlockRanking.

If it is possible for rows to be updated or removed in TESTUserInfo, you would similarly have to create triggers. For an UPDATE trigger, you could do something like: total = total + NEW.total - OLD.total.

See also:

Trigger to update row in another table

Comments