Upsert inside trigger?

classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|

Upsert inside trigger?

ingo
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Upsert inside trigger?

Adrian Ho
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Upsert inside trigger?

ingo


On 16-6-2019 15:11, Adrian Ho wrote:
> Common table expression are not supported for statements inside of
>     triggers.

Ah, I searched the docs for 'upsert', 'with' ...
Thanks.

ingo
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Upsert inside trigger?

Darren Duncan
In reply to this post by Adrian Ho
On 2019-06-16 6:11 a.m., Adrian Ho wrote:
>  From https://sqlite.org/lang_createtrigger.html :
>
> *Syntax Restrictions On UPDATE, DELETE, and INSERT Statements Within
> Triggers*
>
> [...]
>
>    * Common table expression are not supported for statements inside of
>      triggers.

I wonder what the reason for this is, since that page doesn't seem to say.  Why
there are any restrictions at all of this nature in triggers. -- Darren Duncan
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users