'C'||if(count(@layer_id)=0,1,maximum("name")+1)
the source of knowledge: https://stuyts.xyz/2019/10/31/how-to-autonumber-a-field-in-qgis/
Here are the triggers Oisin mentioned:
CREATE TRIGGER YOUR-TABLE-NAME_keep_in_order_delete AFTER DELETE ON YOUR-TABLE-NAME BEGIN UPDATE YOUR-TABLE-NAME SET name_id = name_id-1 WHERE name_id > old.name_id; END (https://sqlite-users.sqlite.narkive.com/gdABXzam/sqlite-sql-to-renumber-values-in-a-column#post4)
CREATE TRIGGER YOUR-TABLE-NAME_populate_name AFTER INSERT ON pointsYOUR-TABLE-NAME BEGIN UPDATE YOUR-TABLE-NAME SET name = (SELECT 'C'|| "name_id") WHERE name_id = NEW.name_id; END
CREATE TRIGGER YOUR-TABLE-NAME_update_name AFTER UPDATE OF name_id ON YOUR-TABLE-NAME BEGIN UPDATE YOUR-TABLE-NAME SET name = (SELECT 'C'|| "name_id") WHERE OLD.name_id > NEW.name_id; END
With sqlitebrowser you can easily insert them into the database (ex.: geopackage). (https://sqlitebrowser.org/)
Required columns in the database:
name_id INTEGER UNIQUE
name TEXT UNIQUE
In QGIS you have to use for default value: if(count(@layer_id)=0,1,maximum("name_id")+1) , but don't check "Apply default value on update"
ps. Tested with qfield, and :)))