QGIS 3 shows and saves raw default-values from spatialite-tables

Question

QGIS shows default values from sqlite tables as string in the edit-form when i try to create a new row. After save, the raw default values are stored in the table. How can i edit in QGIS LTR 3.4.7 a sqlite-database without messing up the default values from the tables?

QGIS-form with new feature and 'wrong' default-values. Feature from sql-insert with id 1 in background.

QGIS-table after save with pointless default-values.

tried "solutions"

• with "sql-insert" everything works as exepected
• same behaviour on different workstations
• same behaviour for sqlite- and spatialite-databases
• same behaviour with or whitout geometry
• same behaviour in QGIS 3.4.7 (LTR) and 3.10
• same behaviour in Windows and Linux
• same behaviour with default-values in a geopackage
• in spatialite InvalidateLayerStatistics(); and UpdateLayerStatistics(); does not help.
• Project Properties -> Data Source -> Check "Evaluate default values on provider side".
• (maybe) relevant google-search results :
  • https://qgis.org/en/site/forusers/visualchangelog30/index.html#feature-detect-literal-default-values-for-spatialite-provider
  • https://github.com/qgis/QGIS/commit/0ae610c5e3dd364385c2fd91f901e706a04101f1

Create statement for the table in the sqlite-database

CREATE TABLE my_table (
    id integer primary key autoincrement,
    comment text,
    created_at_01 text DEFAULT (datetime('now','localtime')),
    created_at_02 text DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO my_table (comment) VALUES ('hello world');

SELECT * FROM my_table;

id          comment      created_at_01        created_at_02      
----------  -----------  -------------------  -------------------
1           hello world  2019-12-09 18:00:12  2019-12-09 17:00:12

Answer 1

My proposal is to create two triggers, so that after each insertion or update of rows, it is verified that if a text string with the shape of the expression is being recorded in that row, the corresponding expression is evaluated:

DROP TRIGGER IF EXISTS "trigger_my_table_insert_evaluate_localtime";
CREATE TRIGGER "trigger_my_table_insert_evaluate_localtime" 
AFTER INSERT ON my_table 
WHEN new.created_at_01 LIKE '%datetime%now%localtime%'
BEGIN
  UPDATE my_table
  SET created_at_01 = (datetime('now','localtime'))
  WHERE id = new.id;
END;

DROP TRIGGER IF EXISTS "trigger_my_table_update_evaluate_localtime";
CREATE TRIGGER "trigger_my_table_update_evaluate_localtime" 
AFTER UPDATE ON my_table 
WHEN new.created_at_01 LIKE '%datetime%now%localtime%'
BEGIN
  UPDATE my_table 
  SET created_at_01 = (datetime('now','localtime'))
  WHERE id = new.id;
END;

Tests were working fine here. In QGIS, when insert, changes are made at save edits. When update too, but you need to close the attribute table and open it again to see the changes.
Please test it, I'm just learning.

---

Reference:

• https://www.sqlite.org/lang_createtrigger.html