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