"jose isaias cabrera" <cabr...@wrc.xerox.com>
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
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
