I wanted to see how SELECT queries within triggers are using indexes
(or not) which of course lead me to https://www.sqlite.org/eqp.html:

    EXPLAIN QUERY PLAN is most useful on a SELECT statement, but may
    also be appear with other statements that read data from database
    tables (e.g. UPDATE, DELETE, INSERT INTO ... SELECT).

However I don't get any output from EQP on non-SELECT queries:

    .version
    -- SQLite 3.9.1 2015-10-16 17:31:12 767c1727fec4ce11b83f25b3f1bfcfe68a2c8b02

    create table t(
        a integer not null,
        b integer not null
    );

    create index t_a on t(a,b);

    create trigger
        t_ai
    after insert on
        t
    for each row
    begin
        insert into t(a,b)
        select a,new.a from t where
            a < new.a and a = b;
    end;

    insert into t(a,b) values(1,1);
    insert into t(a,b) values(2,2);
    insert into t(a,b) values(3,3);
    select * from t order by a,b;
    -- a           b         
    -- ----------  ----------
    -- 1           1         
    -- 1           2         
    -- 1           3         
    -- 2           2         
    -- 2           3         
    -- 3           3         

    explain query plan insert into t(a,b) values(4,4);
    -- No output!

Is the above lack of output expected? Is there some way other than
cutting and pasting and substituing NEW.*/OLD.* values to see what my
triggers are doing?

Mark
-- 
Mark Lawrence

Reply via email to