Page 1 of 1
Update TimeStamp while editing existing records
Posted: Mon Jul 14, 2014 8:40 pm
by 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
Re: Update TimeStamp while editing existing records
Posted: Mon Jul 14, 2014 11:11 pm
by 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:
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.
Re: Update TimeStamp while editing existing records
Posted: Tue Jul 15, 2014 7:15 am
by 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
Re: Update TimeStamp while editing existing records
Posted: Tue Jul 15, 2014 7:45 am
by Villeroy
dreamquartz wrote: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.
Re: Update TimeStamp while editing existing records
Posted: Tue Jul 15, 2014 8:15 am
by 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
Re: Update TimeStamp while editing existing records
Posted: Tue Jul 15, 2014 12:33 pm
by 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!