Upon creation, the trigger below gives a syntax error near INSERT.
Without the trigger surrounding it, the query works well.
The docs give me no clue to what goes wrong.
Ingo
---%<------%<------%<---
CREATE TRIGGER IF NOT EXISTS update_balances
AFTER INSERT ON journal
BEGIN
WITH inup(account_id, value_balance, amount_balance) AS (
--ledgers is a view
SELECT ledgers.account_id,
SUM(ledgers.asset_value),
SUM(ledgers.asset_amount)
FROM ledgers
WHERE ledgers.account_id = 11
)
INSERT INTO balances(account_id, value_balance, amount_balance)
VALUES (
(SELECT account_id FROM inup),
(SELECT value_balance FROM inup),
(SELECT amount_balance FROM inup)
)
ON CONFLICT (balances.account_id)
DO UPDATE
SET value_balance = (SELECT value_balance FROM inup),
amount_balance= (SELECT amount_balance FROM inup)
WHERE account_id = 11
;
END;
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
