"INSERT INTO table AS alias" is invalid inside triggers

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

"INSERT INTO table AS alias" is invalid inside triggers

nomad
I suspect I have found a parsing error in SQLite 3.30.1. Given the
following:

    CREATE TABLE t1(a INTEGER PRIMARY KEY);

The following statement is accepted by the parser:

    INSERT INTO t1 AS original (a) VALUES(1)
      ON CONFLICT DO NOTHING;

However if I wrap it inside a trigger:

    CREATE TABLE t2(b INTEGER);

    CREATE TRIGGER t2_ai
    AFTER INSERT ON t2
    FOR EACH ROW BEGIN

        INSERT INTO t1 AS original (a) VALUES(NEW.b)
          ON CONFLICT DO NOTHING;

    END;

Then SQLite fails to prepare: near "AS": syntax error [for Statement
"CREATE TRIGGER...."]

--
Mark Lawrence
_______________________________________________
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: "INSERT INTO table AS alias" is invalid inside triggers

nomad
On Mon Feb 10, 2020 at 01:34:12AM +0100, [hidden email] wrote:

> I suspect I have found a parsing error in SQLite 3.30.1. Given the
> ...
> However if I wrap it inside a trigger:
>
>     CREATE TABLE t2(b INTEGER);
>
>     CREATE TRIGGER t2_ai
>     AFTER INSERT ON t2
>     FOR EACH ROW BEGIN
>
>         INSERT INTO t1 AS original (a) VALUES(NEW.b)
>           ON CONFLICT DO NOTHING;
>
>     END;
>
> Then SQLite fails to prepare: near "AS": syntax error [for Statement
> "CREATE TRIGGER...."]

I see the same behaviour with 3.31.1. Could I ask the devs if this is
likely to be fixed at some point or will remain as is?

On a related note and perhaps more generally interesting, I find the
UPSERT mechanism quite useful for updating specific multiple rows in a
specific order:

    INSERT INTO
        table
    SELECT
        columns
    FROM
        table
    LEFT JOIN
        other_table
    ON
        join_condition
    WHERE
        where_condition
    ORDER BY
        order_condition
    ON CONFLICT DO UPDATE SET
        x,y,z = (values or some other query)

Previously I have used temporary tables and complicated recursive
triggers to achieve what the above appears to do. I would be very
interested in hearing from those that know better if the above is
reliable and functions the way I think it does.  It is certainly much
easier to understand and I assume more efficient.

Some systems apparently support an UPDATE ... JOIN syntax but I find
the SQLite UPSERT implementation more powerful because of the ORDER BY
possibility.

--
Mark Lawrence
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users