3

Consider the following two (hypothetical) tables

Temperature

* day
* time
* lake_name
* station
* temperature_f

Temperature_summary

* day
* lake_name
* station
* count_readings_over_75f
* count_readings_below_75f

How can I write an SQLite Trigger to update the temperature_summary table on insert. I want to increment the count.

Thank You, Jeff

jrhicks
  • 14,759
  • 9
  • 42
  • 57

2 Answers2

6

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.

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;

You can combine these into one slightly more complex trigger

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;

To insure that there is a row in Temperature_summary to update (a) make a unique index on Temperature_summary's (day, lake_name, station), or make those columns the primary key, and (b) do an insert or ignore in the trigger like so:

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;
Doug Currie
  • 40,708
  • 1
  • 95
  • 119
  • How do I handle the temperature_f <= 75, do I add another trigger or can I add another when statement. Thanks! – jrhicks May 19 '10 at 22:35
  • The second option above handles both cases (above and below) in one trigger. – Doug Currie May 19 '10 at 23:07
  • This assumes I have a record to update, what if I don't have a record to update. I will need to order the triggers and do an insert? Any ideas? – jrhicks May 20 '10 at 15:26
  • If you have a unique index on day, lake_name, station, or make those columns the primary key, then you could do an insert or ignore in the trigger. I've added that as a third example. – Doug Currie May 20 '10 at 16:15
0

A trigger can be created to update the counters in a single trigger. This will arguably improve performance and collect the related items into a single location.

create trigger Temperature_count_insert_trigger_lo after insert on Temperature    begin 
    update Temperature_summary set Temperature_summary set count_readings_over_75f = count_readings_over_75f + (new.temperature_f >= 75 ), count_readings_below_75f = count_readings_below_75f + (new.temperature_f < 75)
    where new.day = day and new.lake_name = lake_name and new.station = station;    end;

It may be necessary to include trigger processing for updates in the Temperature table if these can occur.

Pekka
  • 3,529
  • 27
  • 45