Ask Your Question

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

asked 2014-03-03 20:02:03 +0100

Libre Lyrae gravatar image

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?

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2016-02-19 17:15:36.144349

1 Answer

Sort by » oldest newest most voted

answered 2014-03-03 22:25:07 +0100

frofa gravatar image

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.

edit flag offensive delete link more


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
Libre Lyrae gravatar imageLibre Lyrae ( 2014-03-05 15:07:32 +0100 )edit

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

frofa gravatar imagefrofa ( 2014-03-09 03:35:57 +0100 )edit

Question Tools

1 follower


Asked: 2014-03-03 20:02:03 +0100

Seen: 1,079 times

Last updated: Mar 03 '14