Create composed unique key field with trigger after insert in SQLITE

Question

I have a table with multiple columns and one (unique key) should be a value composed from the values of other two columns.

CREATE TABLE batches (
    id   TEXT PRIMARY KEY UNIQUE,
    name TEXT NOT NULL,
    project_id INTEGER);

On each insert, I want to generate the id based on the value of 'name' and 'project_id' (this one can be null):

INSERT INTO batches (name,project_id) VALUES
     ('21.01',NULL),
     ('21.01',1),
     ('21.02',2);

So, I have created a table TRIGGER but doesn't execute.

CREATE TRIGGER create_batches_id 
AFTER INSERT ON batches FOR EACH ROW
BEGIN
    UPDATE batches 
    SET id = SELECT quote(name ||"_"|| (CASE project_id 
            WHEN NULL THEN '' ELSE project_id END )
        FROM batches WHERE rowid = (SELECT MAX(rowid) FROM batches))
    WHERE rowid = (SELECT MAX(rowid) FROM batches);
END;

Error:

SQL Error [1]: [SQLITE_ERROR] SQL error or missing database (near "SELECT": syntax error)

I expect:

id = 21.01_
id = 21.01_1
id = 21.01_2

What am I doing wrong? If I run only the SELECT/CASE statment it returns ok: '21.01_2'

I have also tried without the quote() function, no success.

UPDATE I:

I have managed to execute the whole create trigger statement (parenthesis were missing):

CREATE TRIGGER create_batch_id 
AFTER INSERT ON batches FOR EACH ROW
BEGIN
    UPDATE batches 
    SET id = (SELECT name ||"_"|| (CASE project_id WHEN NULL THEN 0 ELSE project_id END ) FROM batches WHERE rowid = (SELECT MAX(rowid) FROM batches) )
    WHERE rowid = (SELECT MAX(rowid) FROM batches);
END;  

It seems my editor (DBeaver) has a glitch with the following new line character. If it is inside the selection it runs into this exception (or I am missing something):

SQL Error [1]: [SQLITE_ERROR] SQL error or missing database (incomplete input)

If I manually select only the above lines (from CREATE to ;), the trigger is created, however, not the expected result. If value in project_id is NULL, no id value is created.

Answer 1

Don't add the column id in the table.
Instead define the combination of name and project_id as the PRIMARY KEY of the table, so that it is also UNIQUE:

CREATE TABLE batches (
    name TEXT NOT NULL,
    project_id INTEGER,
    PRIMARY KEY(name, project_id)
)

Then, whenever you need that id you can run a query:

SELECT name || '_' || COALESCE(project_id, '') AS id, 
       name, 
       project_id
FROM batches

Or create a view:

CREATE VIEW v_batches AS
SELECT name || '_' || COALESCE(project_id, '') AS id, 
       name, 
       project_id
FROM batches

and query the view:

SELECT * FROM v_batches

See the demo.

Or if your version of SQLite is 3.31.0+ you can have the column id as a generated column:

CREATE TABLE batches (
    name TEXT NOT NULL,
    project_id INTEGER,
    id TEXT GENERATED ALWAYS AS (name || '_' || COALESCE(project_id, '')),
    PRIMARY KEY(name, project_id)
);