Re: "INSERT INTO table AS alias" is invalid inside triggers

On Mon Feb 10, 2020 at 01:34:12AM +0100, [email protected] 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
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users