Default Value ON UPDATE Liquibase

Question

I am using Liquibase for generating a MySQL and a HSQLDB databases.
In several tables I have a column called 'last_modified' which is the TIMESTAMP of the last update on that particular record.

<changeSet author="bob" id="7">
    <createTable tableName="myTable">
        <column autoIncrement="true" name="id" type="INT">
            <constraints nullable="false" primaryKey="true" />
        </column>
        <column name="name" type="VARCHAR(128)">
            <constraints nullable="false" />
        </column>
        <column name="description" type="VARCHAR(512)" />
        <column defaultValueBoolean="true" name="enabled" type="BIT">
            <constraints nullable="false" />
        </column>
        <column name="last_modified" type="TIMESTAMP"/>
    </createTable>
    <modifySql dbms="mysql">
        <append value=" engine innodb" />
    </modifySql>
</changeSet>

I noticed that if I use MySQL, the generated SQL for that column is:

`last_modified` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

While if I use HSQLDB, in case of update nothing happens, but I would like to have the same behaviour of the MySQL database with a default value on update equals to the CURRENT_TIMESTAMP.

How can I set the CURRENT_TIMESTAMP as a default value ON UPDATE?

Answer 1

You can't do this with a default value. The MySQL behaviour is non-standard and not supported by other databases. The proper way to do this is with a TRIGGER which is defined as BEFORE UPDATE and sets the timestamp each time the row is updated.

Update: From HSQLDB version 2.3.4 this feature is supported. For example: CREATE TABLE T1(ID INT, last_modified timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP NOT NULL). Note the NOT NULL constraint must appear after the DEFAULT and ON UPDATE clauses.

Answer 2

we actually use Liquibase for the exact same use-case. You'll want to make an update trigger as fredt described. Otherwise, you can't be sure the update will occur on other databases besides MySQL. Your changeset tag will log something like this:

<sql splitStatements="false">
  CREATE TRIGGER update_${tableName}_trg
    BEFORE UPDATE ON ${tableName}
      FOR EACH ROW BEGIN
        SET NEW.updated_at = NOW();
      END
</sql>

Also, I have a question on Stack Overflow about how to refactor some of this code you can see at How do you refactor similar looking code and provide params in Liquibase?.

Answer 3

Or you could try this, as you have already have modifySql tag added:

<column defaultValue="CURRENT_TIMESTAMP"
             name="timestamp"
             type="TIMESTAMP">
            <constraints nullable="false"/>
 </column>
<modifySql dbms="mysql">
    <regExpReplace replace="'CURRENT_TIMESTAMP'" with="CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP"/>
    <append value=" engine innodb" />
</modifySql>

Liquibase 3.1.1 does not produce what you described above. I have to deal with it as given above