sql - How to automatically create rows in table after updating another table -

i developing web application. in database of web application, have table named daily in making entries on daily basis. let has 3 columns a, b , c. have table named monthly in store same a, b, c values on monthly basis. want when 1 month completes i.e no of entries in daily table becomes 30 or 31 new row gets created automatically in monthly table a, b, , c values contain sum of values in daily table of corresponding a, b, c values. want know how can using sql , if not other way ?

gordon linoff's response way go, depending on database, os, programming language etc.

another way create "trigger" on daily table, automatically performs summarization when row last day of month inserted. please note: triggers tricky, , can complicate maintenance , replication.

i'll make little more concrete, make explanation easier. also, details of syntax depend on specific choice of database. answer use postgresql syntax.

here's daily table. have added column contain date, , made primary key.

create table daily (   daily_date date not null,   integer not null,   b integer not null,   c integer not null,   constraint daily_pkey primary key (daily_date) ); 

and here monthly table:

create table monthly (   monthly_date date not null,   a_sum integer not null,   b_sum integer not null,   c_sum integer not null,   constraint monthly_pkey primary key (monthly_date) ); 

now create procedure called when last daily row of month inserted. in postgresql, special type called trigger function. details vary wildly depending on database.

create or replace function summarize_month()   returns trigger $body$declare      month_begin date :=         make_date(             cast (extract(year new.daily_date) integer),             cast (extract(month new.daily_date) integer),             1);     month_end date :=            month_begin + interval '1 month' - interval '1 day'; begin     insert monthly(monthly_date, a_sum, b_sum, c_sum)     (select month_begin, sum(a), sum(b),sum(c)     daily     daily_date between month_begin , month_end);     return null;  end;$body$   language plpgsql volatile   cost 100; 

this procedure figures out beginning , ending date of month summarized, based on daily_date of row inserted. inserts new row monthly table, containing summarized data computed select statement.

finally, attach trigger function daily table, , tell execute when row last day of month inserted.

create trigger summarize_daily_to_monthly   after insert   on daily   each row   when ((date_part('month'::text, new.daily_date) <>           date_part('month'::text, (new.daily_date + 1))))   execute procedure summarize_month(); 

i know long answer sounds simple problem. please feel free ask if need further clarification.