Hello! I think I've hit a wall when it comes to designing a schema & SQLite's current capabilities, and hoped an SQLite expert could confirm.

This is a simplified schema for a double-entry bookkeeping system, with a possibly-handy trigger to ensure data integrity:

CREATE TABLE journal (
  id        INTEGER PRIMARY KEY AUTOINCREMENT,
  narrative TEXT NOT NULL DEFAULT ""
);

CREATE TABLE trans (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  journal_id INTEGER NOT NULL,
  account TEXT NOT NULL,
  cr INTEGER,
  FOREIGN KEY(journal_id) REFERENCES journal(id)
);

CREATE TRIGGER verify_trans AFTER INSERT ON journal
BEGIN
  SELECT
    CASE (SUM(cr) <> 0) WHEN 1 THEN RAISE(ROLLBACK, "Transactions don't balance") END
    -- etc.
  FROM 
    trans 
  WHERE 
    trans.journal_id=new.id;
END;

What I want to do is insert a row into "journal", and rows into "trans" which balance the journal entry (i.e. the totals add up to zero).

My ideal would be e.g.

WITH j AS  ( INSERT INTO journal ... RETURNING id ) 
INSERT INTO trans ... VALUES (j.id ...), (j.id ...)

But I understand RETURNING values can't be brought into other statements, only back to the application.

Or is there some other way round I might have missed, so a trigger like this could work?

But I understand RETURNING values can't be brought into other statements, only back to the application.

Or is there some other way round I might have missed, so a trigger like this could work?

No. No.

Your implementation cannot work, in any version of SQL.

Bookkeeping (Inventory Control of Money) is a rather simple, you simply (like any other goop or stuff control system) maintain mass balance. This means that the thing you are inventorying is neither created nor destroyed, but simply moved from place to place.

Your master journal entries should have two accounts -- the one that is debited and the one that is credited, for the same singular amount, in the same transaction. That is, something like:

create table Accounts
(
  id integer primary key,
  AccountNo text not null collate nocase unique,
  Balance real not null default 0
);
create table Journal
(
  id integer primary key,
  CreditAccount integer not null references Accounts(id),
  DebitAccount  integer not null references Accounts(id),
  Amount real not null
  -- other data, such as dates, notes, comments, etc
);
create trigger journal_del before delete on journal
begin
 select raise('You cannot modify a transaction after it is journalled');
end;
create trigger journal_upd before update on journal
begin
 select raise('You cannot modify a transaction after it is journalled');
end;
create trigger journal_ins after insert on journal
begin
  update Accounts 
     set Balance = Balance + new.Amount
   where id == new.CreditAccount;
 update Accounts
    set Balance = Balance - new.Amount
  where id == new.DebitAccount;
end;

Thank you! I'm not trying to access the new row ID in a trigger (I already get that accessing via new.), but insert one new journal entry & multiple new transactions, with the transactions bearing the new journal ID.

I see this question asked of other databases, and they seem to allow it using INSERT ... RETURNING in a CTE, allowing straightforward access to the new id.

So I wanted to check whether there was some other way that I could do it in SQLite.

Right now I might relax the data model so that the checks only trigger when I set e.g. a "finalised_at" column on the journal. That would allow me to insert the journal (query 1), insert the transactions (query 2), and then set the "finalised_at" column (query 3) to trigger the checks and make the data visible to the application.

At the moment I'm just experimenting with schemas and performance to see what might work harmoniously & performantly with SQLite.

Thanks! Using one row per journal would make this problem simpler, but that loses the flexibility of using more than 2 accounts. Also, (and maybe I should have abstracted my problem domain a bit better to make clear this was an SQL question, the domain is something I'm researching) but I wouldn't use a floating-point data type to represent money.