Assignment 4 – Triggers and Transactions

Part 1.

In addition to our CheckIns table, add a table to your website to store users (uid, handle, first_name, last_name, zip_code) of your app. Add a table that holds a score called scoreboard (uid, num_checkins, last_checkin_sec, avg_period_sec) for each user. Add a uid field to the CheckIns table. Set default uid to 1 (at first).

Part 2.

Add a trigger that will insert a scoreboard record when a new user is added.

INSERT INTO scoreboard SET num_checkins=num_checkins,
   avg_period_sec=0, last_checkin_sec=TO_SECONDS(NOW()),uid=NEW.uid

Part 3.

Add a trigger that will update the scoreboard record when a user checks in. Do not update scoreboard for a checkin sooner that one minute after the previous checkin.

UPDATE scoreboard SET
    avg_period_secs = (avg_period_secs*num_checkins + (TO_SECONDS(NOW()) - last_checkin_secs))/(num_checkins + 1),
    last_checkin_secs = TO_SECONDS(NOW()),
    num_checkins=num_checkins+1
WHERE uid=NEW.uid    

Part 4.

Make simple web pages to test triggers along with your app.