用于更新摘要计数的 SQLite 触发器 [英] SQLite trigger to update Summary Counts
问题描述
考虑以下两个(假设的)表格
Consider the following two (hypothetical) tables
温度
* day
* time
* lake_name
* station
* temperature_f
Temperature_summary
Temperature_summary
* day
* lake_name
* station
* count_readings_over_75f
* count_readings_below_75f
如何编写 SQLite 触发器来更新插入时的 temperature_summary 表.我想增加计数.
How can I write an SQLite Trigger to update the temperature_summary table on insert. I want to increment the count.
谢谢,杰夫
推荐答案
这假设您在插入当天的温度之前已经创建了 day/lake_name/station 的记录.当然,您可以添加另一个触发器来执行此操作.
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;
确保 Temperature_summary 中有一行需要更新 (a) 在 Temperature_summary 的 (day,lake_name,station)
上创建唯一索引,或将这些列设为主键,以及 (b)在触发器中执行插入或忽略,如下所示:
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;
这篇关于用于更新摘要计数的 SQLite 触发器的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!