On 2014/02/11 20:07, Gert Van Assche wrote:
All,
Does anyone know if it is possible for a date field to be automatically
incremented with a month when a new record is created?
If the last row contains "2013-01-01" in the DateField then the DateField
of the new row should automaticllay be "2013-02-01".
Updating a value is easy, a trigger can do that. Increasing a date by a month
is very easy.
Finding the last added date, not so easy...
It is of course possible with a query, when making some assumptions, but I would suggest keeping it somewhere else. However, in the
interest of expedience, I will assume that the date that should be incremented is the very last added date, I will also assume the
date field is a Key (or Index if you will) and called nDate, and I will assume the the primary Index is called ID and it is
autoincremented and lastly assume the table is called Table1 - if these assumptions are all true, you could possibly do it like this:
CREATE TRIGGER IF NOT EXISTS Trig_Table1_setLastDate
AFTER INSERT ON Table1 FOR EACH ROW
BEGIN
UPDATE Table1 SET nDate = (SELECT date(C.nDate,'+1 month') FROM `Table1` AS C
ORDER BY C.ID DESC LIMIT 1) WHERE ID = NEW.ID;
END;
Note that if the ID is not auto-incremented, it might end up being sorted wrongly and the highest ID might not be the very last
added ID. You can sort by date too if the last added date is definitely the highest date, etc. The DESC makes sure the highest is
sorted first, and the limit makes sure we pick only the 1st highest. The C alias makes sure the SQL engine does not get confused
with the sub-query table. The Trigger makes sure that whatever new line is added that the new ID's record get the update.
Pretty straight forward _IF_ all the assumptions hold true - else you may need
some more tricks to do it.
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
