I'm trying to create an SQLite trigger to update balance for a particular account code.

accounts table :

CREATE TABLE accounts (
    year      INTEGER   NOT NULL,
    month     INTEGER   NOT NULL   CHECK(month BETWEEN 1 AND 12),
    amount    REAL      NOT NULL   CHECK(amount >= 0),
    balance   REAL,
    code      INTEGER   NOT NULL
);

When a new row is inserted I want the balance value of the new row to reflect OLD balance NEW amount. But this trigger does not recognize the lagging balance value and I cannot figure out why:

CREATE TRIGGER trg_accounts_balance
AFTER INSERT ON accounts
BEGIN
    UPDATE accounts
    SET balance = (
        SELECT
            lag(balance, 1, 0) OVER (
                PARTITION BY code
                ORDER BY month
            )   NEW.amount
        FROM accounts
    )
    WHERE rowid = NEW.ROWID;
END;

If I insert one row per month, I expect my data to look like:

But I get:

What am I doing wrong?

CodePudding user response：

The query:

SELECT
    lag(balance, 1, 0) OVER (
                PARTITION BY code
                ORDER BY month
            ) 
FROM accounts

returns as many rows as there are in the table and SQLite picks the first (whichever it is) to return it as the result so that it can use it to add NEW.amount.
There is nothing that links this value to the specific row that was inserted.

Instead, use this:

CREATE TRIGGER trg_accounts_balance
AFTER INSERT ON accounts
BEGIN
    UPDATE accounts
    SET balance = COALESCE(
       (
        SELECT balance
        FROM accounts
        WHERE code = NEW.code
        ORDER BY year DESC, month DESC
        LIMIT 1, 1
       ), 0)    NEW.amount
    WHERE rowid = NEW.ROWID;
END;

The subquery returns the previous inserted row by ordering the rows of the specific code descending and skipping the top row (which is the new row).

See the demo.