HSQLDB & Optimistic Locking

Question

In MySQL, it is possible to define a table column whose value is always updated by the database manager:

CREATE TABLE widgets (
    widget_id         INT NOT NULL,
    widget_version    TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
)

Above, anytime a new [widgets] entity gets created, the current timestamp is used for its version column. And, anytime a write/update occurs to that entity, the system will update version with (again) the current timestamp.

Is there a way to do this in HSQLDB 2.3.x? My Google Fu and perusing of the HSQLDB docs turned up nadda.

Answer 1

I just spoke to Mitch Connor, founder and primary maintainer of HSQLDB, on IRC, and apparently HSQLDB was never built with the intention of handling optimistic locking.

Mitch recommended I try H2 instead.

Answer 2

HSQLDB does not support an update clause in the DEFAULT expression.

HSQLDB does supports triggers for this purpose.

CREATE TRIGGER t BEFORE UPDATE ON wiget_version
REFERENCING NEW AS newrow FOR EACH ROW
BEGIN ATOMIC
   SET newrow.widget_version = LOCALTIMESTAMP;
END