Update TimeStamp while editing existing records

[dreamquartz]

Hi Villeroy,

How are the celebrations going?
Germany SOCCER WORLD CHAMPION.

I have a question:
I understand you are not fond of macros and so am I.

I found something concerning a TimeStamp, and there are a few discussions going on right now.
You provided an exellent example viewtopic.php?t=69762&p=316045#p316045, but I need to be able to update my timestamp when editing information, already present.
Reading the following viewtopic.php?p=184958#p184958 am I to understand that there is no way to avoid macros concerning this?

Thanks in advance,

Dream

[Villeroy]

Tested with HSQL2 and a date column "D" in a table "Data":

Code: Select all

CREATE TRIGGER "NewDateOnUpdate" BEFORE UPDATE ON "Data"
   REFERENCING NEW AS "newrow" FOR EACH ROW
   BEGIN ATOMIC
       SET "newrow"."D" = CURRENT_DATE;
   END

After running this via Tools>SQL... any updated record in "Data" gets today's date in column "D" no matter if the edit is done in a Base form or any kind of grid view in Base or any other frontend.

This turns the magic off:

Code: Select all

DROP TRIGGER "NewDateOnUpdate"

HSQL 1.8 is somewhat different in syntax. I don't fully understand this feature. Until now I used to INSERT new records with new values and time stamps instead of time-stamping UPDATEs.

[dreamquartz]

Hi Villeroy,

I am still running embedded 1.8.
I cannot test your suggestion, because it does not recognize "REFERENCING".
To be honest, I don't care on how it is done at this point, as long if there is no macro involved.

I do not quite follw what you mean with that you used "to insert new records with new values and timestamps instead of time-stamping UPDATEs".

Can you please eleborate?

Dream

[Villeroy]

I do not quite follw what you mean with that you used "to insert new records with new values and timestamps instead of time-stamping UPDATEs".

When some list item gets a change, I enter the change into a "new-record-only" form with a new time stamp. This way I keep the information about the status before the change.

Code: Select all

ItemID | Value | Stamp
12345 | 12 | 2013-12-04 23:15:12
...
12345 | 99 | 2014-07-15 07:45:00

By inserting new records I can query the status at any time and the course of values for each item over time without updating timestamps of existing records.

[dreamquartz]

That makes sense.
But my issue is with having existing individual records, i.e. information about people, that need to be updated.
It is a simple principle, but it appears to be quite complicated.

Dream

[Villeroy]

With HSQL2 and other databases it is a matter of running one SQL command once. According to Sliderule, this does not work with HSQL1. viewtopic.php?f=13&t=70701&p=316792#p316792

How are the celebrations going?
Germany SOCCER WORLD CHAMPION.

During the past weeks I heard some vibrant Brazilian music via YouTube which was way more entertaining than their uncontrolled emotion and self delusion on the soccer field. Right in this moment the German team celebrates a confetti parade in Berlin. Yep, I think they deserved the cup. Part of this success was the way how they managed to keep some distance from the silly public during this tournament. Today the high tide of commercials and political propaganda is swashing back over the Atlantic. I turn off the TV right NOW!