sqlite trigger insert convert to json-CodePudding

I'm using SQLite 3.37.2 through Python 3.10.5, and editing the database with DBeaver - maybe it's not always at its best when reporting database errors.

Anyway, I want to convert a text (JSON) in SQLite proper JSON format upon insertion, by calling its json() function.

I thought about adding a simple trigger to handle that, but I can't figure out what's wrong in my syntax:

CREATE TRIGGER t1_before_insert AFTER INSERT ON t1 FOR EACH ROW
    BEGIN  
        SET NEW.json:=json(NEW.json);
    END
;

This is the latest version I attempted, I always get an error like

SQL error or missing database (near "SET": syntax error)

I tried:

SET NEW.json:=json(NEW.json)
SET NEW.json=json(NEW.json)
SELECT NEW.json:=json(NEW.json)
using an AFTER INSERT trigger

but none worked.

CodePudding user response：

You can't change the value of a column of the new row like that.

You must update the table in an AFTER INSERT trigger:

CREATE TRIGGER t1_after_insert AFTER INSERT ON t1 FOR EACH ROW
BEGIN  
  UPDATE t1
  SET json = json(NEW.json)
  WHERE t1.id = NEW.id;
END;

Change id to the table's primary key.

See the demo.