On 16/6/19 8:37 PM, ingo wrote: > 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.
From https://sqlite.org/lang_createtrigger.html : *Syntax Restrictions On UPDATE, DELETE, and INSERT Statements Within Triggers* The UPDATE <https://sqlite.org/lang_update.html>, DELETE <https://sqlite.org/lang_delete.html>, and INSERT <https://sqlite.org/lang_insert.html> statements within triggers do not support the full syntax for UPDATE <https://sqlite.org/lang_update.html>, DELETE <https://sqlite.org/lang_delete.html>, and INSERT <https://sqlite.org/lang_insert.html> statements. The following restrictions apply: [...] * Common table expression are not supported for statements inside of triggers. > 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; At a minimum, you'll have to factor out that WITH clause. _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
