StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>This assumes you have already created the record for the day/lake_name/station before inserting temperatures on that day. Of course, you could add another trigger to do that.</p> <pre><code>create trigger Temperature_count_insert_trigger_hi after insert on Temperature when new.temperature_f >= 75 begin update Temperature_summary set count_readings_over_75f = count_readings_over_75f + 1 where new.day = day and new.lake_name = lake_name and new.station = station; end; create trigger Temperature_count_insert_trigger_lo after insert on Temperature when new.temperature_f < 75 begin update Temperature_summary set count_readings_below_75f = count_readings_below_75f + 1 where new.day = day and new.lake_name = lake_name and new.station = station; end; </code></pre> <p>You can combine these into one slightly more complex trigger</p> <pre><code>create trigger Temperature_count_insert_trigger after insert on Temperature begin update Temperature_summary set count_readings_below_75f = count_readings_below_75f + (new.temperature_f < 75), count_readings_over_75f = count_readings_over_75f + (new.temperature_f >= 75) where new.day = day and new.lake_name = lake_name and new.station = station; end; </code></pre> <p>To insure that there is a row in Temperature_summary to update (a) make a unique index on Temperature_summary's <code>(day, lake_name, station)</code>, or make those columns the primary key, and (b) do an insert or ignore in the trigger like so:</p> <pre><code>create trigger Temperature_count_insert_trigger after insert on Temperature begin insert or ignore into Temperature_summary values (new.day, new.lake_name, new.station, 0, 0); update Temperature_summary set count_readings_below_75f = count_readings_below_75f + (new.temperature_f < 75), count_readings_over_75f = count_readings_over_75f + (new.temperature_f >= 75) where new.day = day and new.lake_name = lake_name and new.station = station; end; </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload