SQLite Forum

How to detect invalid trigger w/o actually running it?

How to detect invalid trigger w/o actually running it?

(1) By ddevienne on 2021-07-08 08:40:00 [link] [source]

Hi. We've had a mishap with a trigger referencing an unknown table,
which was detected too late, by internal users. The SQL script doing
the upgrade (that added the faulty trigger) was run during tests,
and installed successfully w/o errors, but of course actually using it
yielded errors.

Validating all triggers at runtime is currently not something we can do...

So is there a way to quickly detect any invalid objects, like triggers,
views, etc... which does not implies running them (via a DML statement)?

Something we could run post-schema-upgrade or post-schema-creation, to
avoid a repeat of the above mishap?

Thanks, --DD

PS: Note that we may have pragma_legacy_alter_table, I'm not 100% sure,
on the connection doing the upgrade, if that makes a difference.

(2) By anonymous on 2021-07-08 12:09:53 in reply to 1 [link] [source]

We often run all queries prefixed with EXPLAIN QUERY PLAN to detect syntax errors.

Two warnings: make sure your query consist of a single statement. Otherwise your the second one will actually be executed, e.g. EXPLAIN QUERY PLAN SELECT 1; DROP TABLE employees

If your script uses CREATE statements, which will only be executed with EXPLAIN QUERY PLAN CREATE ..., later queries that refer to these objects will of course fail because the objects do not exist. Similar for ALTER TABLE.

(3) By ddevienne on 2021-07-08 17:24:39 in reply to 2 [link] [source]

Thanks for the suggestion. Although with triggers, it can't be just SELECTs,
and coming up with all kinds of dummy statements to explain, so one can be
sure there's no mistake hiding in the schema seems less than ideal.

Surely there has got to be a better way?

And if not at the moment, shouldn't there be a pragma check_schema in the future,
that checks everything declared in sqlite_schema is OK, referencing existing tables/views/columns, that is opt-in.

(4) By Larry Brasfield (larrybr) on 2021-07-08 17:45:52 in reply to 3 [source]

Would it suffice to get your trigger-induced SQL past sqlite3_prepare()? It would be easy to do this in Perl or C (or any other language/library combo exposing prepare.)

(5) By Keith Medcalf (kmedcalf) on 2021-07-08 18:17:24 in reply to 4 [link] [source]

There is, presumably, already code which checks the validity/consistency of the schema which is used following/before ALTER TABLE commands. If this were a separate function (internally) then perhaps it could be exposed via a PRAGMA?

As in pragma check_schema which returns OK if the schema passes consistency checks, otherwise it throws an error for whatever the first thing is it finds wrong (ie, a view references a non-existing table/column/etc).

While this might not solve the entire problem it could, however, give an indication that the schema is at least internally consistent.

Just an idea.