"Igor Tandetnik" wrote in message news:gl9sa9$be...@ger.gmane.org... > "jose isaias cabrera" wrote in message > news:740bad1423a549f6ba68c1e02ab7a...@stso.mc.xerox.com >> Let us imagine the following table: >> CREATE TABLE PMTime (id integer primary key, rec integer, date, secs >> integer); >> >> What the program does is to keep track that the secs will not add to >> more than 28800 for any date. If it does, a new PMTime record is >> added with the rest of the secs that are over 28800 and assigned to >> the next date that is not Saturday or Sunday and to the same rec. I >> have been looking into triggers, but I can not even come up with >> anything close. > > Something like this perhaps: > > create trigger PMTime_insert after insert on PMTime > when (select sum(secs) from PMTime where date = new.date) > 28800 > begin > > -- insert new record with the overflow > insert into PMTime(rec, date, secs) values ( > new.rec, > date(new.date, (case strftime('%w', new.date) when '5' then '3' when > '6' then '2' else '1' end) || ' days'), > (select sum(secs) from PMTime where date = new.date) - 28800 > ); > > -- update just-inserted record and shave off the overflow > update PMTime set secs = > secs - (select sum(secs) from PMTime where date = new.date) + 28800 > where id = new.id; > > -- if the previous update brougth secs down to 0, delete the record > entirely > delete from PMTime > where id = new.id and secs = 0; > > end; > > Igor Tandetnik
thanks, Igor. I will give this a try... josé _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
