You need an UPDATE trigger for this, since the comparison requires knowledge of the old and new values.
-----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 ___________________________________________ Gunter Hick | Software Engineer | Scientific Games International GmbH | Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 1 80100 - 0 May be privileged. May be confidential. Please delete if not the addressee. _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
