Op vr 27 dec. 2019 om 13:12 schreef Hick Gunter <h...@scigames.at>: > You need an UPDATE trigger for this, since the comparison requires > knowledge of the old and new values. >
Of-course. I should have thought of that. :'-( I will look into that this weekend. In my case it is not important (I do not expect to insert records from the past), but I also add that it will not be higher as record from a later date. -----Ursprüngliche Nachricht----- > Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] > Im Auftrag von Cecil Westerhof > Gesendet: Freitag, 27. Dezember 2019 13:05 > An: SQLite mailing list <sqlite-users@mailinglists.sqlite.org> > Betreff: [EXTERNAL] [sqlite] Only enter higher values in table > > Just to have a way to see my progress at Udemy, I created the following > table and view: > CREATE TABLE rawSummaryUdemy ( > date TEXT NOT NULL DEFAULT CURRENT_DATE, > total INTEGER NOT NULL, > completed INTEGER NOT NULL, > > CONSTRAINT formatDate CHECK(date = date(strftime('%s', > date), 'unixepoch')), > CONSTRAINT notInFuture CHECK(date <= date()), > CONSTRAINT totalIsInt CHECK(TYPEOF(total) = 'integer'), > CONSTRAINT completedIsInt CHECK(TYPEOF(completed) = 'integer'), > CONSTRAINT totalGEZero CHECK(total >= 0), > CONSTRAINT completedGEZero CHECK(completed >= 0), > CONSTRAINT completedLETotal CHECK(completed <= total), > > PRIMARY KEY(date) > ); > CREATE VIEW summaryUdemy AS > SELECT * > , total - completed AS toComplete > FROM rawSummaryUdemy > ; > > If this can be done better: let me know. > > Normally speaking total and completed should never decrease. It is not > really important, but just as an exercise: is it possible to add > constraints so that you cannot enter a total, or a completed that is lower > as the previous one? > -- Cecil Westerhof _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
