On Wed, Feb 24, 2016 at 05:24:44PM -0700, David G. Johnston wrote: > Then you must record the "INSERT/UPDATE time" into the table, as a > constant, and refer to that value instead of having "now()" which happens > to be correct at the time of the insert/update but is not correct at any > future point. > > So: > > ts_last_change DEFAULT now() > and > CHECK (... > (is_ongoing is true) AND (ts_end > > ts_last_change > ) > ) > > In this way a restoration will restore both the historical ts_last_change > and the value of is_ongoing that matched. Thanks for this suggestion. In fact, my table data audit system already provides for such a column, namely .modified_when. However, I still like to have the trigger to set .is_ongoing to FALSE when .ts_end goes into the past (as of the time of an UPDATE to the row), regardless of the fact that the constraint between .ts_end and $TS_LAST_CHANGE (here: .modified_when) is formally fulfilled. The reason being that that does not really represent the business intent of the constraint, which is WHENEVER therapy.is_ongoing is true, THEN (at the time of WHENEVER) therapy.ts_end must be unknown OR in the (real world) future :-) Karsten -- GPG key ID E4071346 @ eu.pool.sks-keyservers.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
