How do I make a "Date Modified" field in Base?

[LibreLyrae]

I have tried the following SQL to make a DATE field in Base that will update when the record has been modified to show the date it was last updated.

“DateModified” DATE DEFAULT CURRENT_DATE — this gives me the date that the record was created, but will not update when modified… so
I searched online and found someone suggested to instead set my field like this:
“DateModified” DATE DEFAULT CURRENT_TIMESTAMP — again this is a set timestamp for the date the field was created. I cannot seem to get it to update when the record is modified.

Is there a way using SQL to get a field to update the date when the record has been modified?

[frofa]

You will need to use a TRIGGER (SQL) or a Macro (script) to do the job. See THIS DISCUSSION. Note that you cannot use TRIGGERS with HSQLDB v1.8 (the default database ‘engine’ used by Base). You will have to ‘split’ your database and upgrade to HSQLDB v.2.3.x in order to use TRIGGERS - see tutorial HERE.

[LibreLyrae]

I tried the following Trigger but got the error “Unexepected Token ON”:
CREATE TRIGGER TrgDateModified ON “TableName”
AFTER UPDATE
AS
UPDATE “TableName”
SET “DateModified” =GETDATE()
WHERE ID IN (SELECT DISTINCT ID FROM Inserted)

• Note I am using the split HSQLDB v2.3x not HSQLDB v1.8

[frofa]

Did you try Sliderule’s code posted at the end of THIS DISCUSSION (as mentioned above)?