Say I have two tables: runners and waypoint_checkins. I want to add waypoint checkin entries but I can never add a waypoint if the runner has finished the race, (see below for schema). Finally. A runner can never check into a waypoint if the waypoint is listed as closed.
table: runners
id | name | is_finished
---+------+------------
15 | abe | false
table: waypoints
id | currently_open
----+---------------
1 | true
2 | true
3 | false
... | ...
table: waypoint_checkins
runner_id | waypoint_id | time
----------+-------------+-------
15 | 1 | 5:00
15 | 2 | 5:23
15 | 4 | 5:52
So on a checkin I'm given (runner_name, waypoint_id, time). First I need to find the runner and make sure they are still running:
SELECT id, is_finished FROM runners WHERE name = %name;
Then I need to check if the runner is at an open waypoint;
SELECT currently_open FROM waypoints WHERE id = %waypoint_id;
Finally I need to insert a new waypoint checkin:
INSERT INTO waypoint_checkins VALUES (%runner_id, %wp_id, %time);
But between query 1 and 3 the runner may have finished. How can I do the insert statement where I can guarantee that the earlier select is still valid?
trigger
inrunners
table, when change theis_finished
column become true, then make theINSERT
. – oNare Jul 5 '15 at 13:41