SQLite Forum

.selecttrace unknown and strange errors
You are confusing the state "compile the code" with the state "run the code".

When you "prepare" a statement using the sqlite3_prepare* API you are "generating the code".  In order to "generate the code" **all** of the applicable source code must be parsed and "compiled".  This means that **ALL** the source code must be checked for errors (in this case all the applicable or could be applicable triggers).

Just like when you "compile a program" which consists of 47 source code files, an error in **ONE** of those 47 source code files is flagged by the compiler, and no "executable" is produced.  Instead you get an error message telling you about the error.

This is the error you are seeing.

Once you have managed to fix all the errors in your source code files, then the compiler is able to produce an "executable" (which in the case of SQLite3 is called a "statement" (VDBE program) and is the output of the compiling (prepare) process).

When you "compile" a bunch of source code files into an "executable", you cause that code to enter into the "executing" state by running the resulting executable.

In the case of SQLite3 when you "compile" (prepare) a valid "program" the output is a "statement" (VDBE program).  You cause this program to execute by calling the sqlite3_exec API on this statement.  In both cases you pass "parameters" into the program (for a computer program that is arguments on the command line, with SQLite3 that is by using the sqlite3_bind API).  Some variables may be set within the program itself.  Just because some file says "a2 = 2" does not mean anything with respect to compilation.  The value only has effect at execution time.

However, the result of "executing" a statement, sqlite3_exec, is very limited.  It may tell you SQLITE_DONE if it ran to completion, SQLITE_ROW if it has an intermediate row for you to do something with, or another applicable error message (such as SQLITE_BUSY, SQLITE_LOCKED, or a very small set of SQLITE_ERROR all dealing with **execution** problems (and never source code compilation problems).

Maybe perhaps the CLI should indicate whether the error is a "PREPARE ERROR" or an "EXEC ERROR" to make this clearer to you.  At the moment it does not because there is an assumption that you know that certain classes of errors can only be returned by the PREPARE processing and that other errors can only be returned by the EXEC processing (if it even matters at all), and there is almost no overlap (SQLITE_NOMEM, SQLITE_MISUSE, SQLITE_BUSY, SQLITE_LOCKED, and perhaps a few others are the only ones, and if you get one of those you are "dunfer" anyway).

Furthermore I would observe that you seem to presume that the WHEN clause in a trigger is a **compile** time directive.  It is not.

In the case of triggers:  
`{BEFORE|AFTER|INSTEAD OF}` is a compile time directive controlling whether the code is to be inserted before, after, or instead of the operation chosen.  
`{INSERT|UPDATE|DELETE}` are compile time directives controlling the statements to which the code is applicable.  
`[OF <column list>] ON <name>` is a compile time directive controlling when the statements are to be included in the code.  
`[WHEN <expression>]` is a runtime entry condition.

In this particular case, you are doing an UPDATE A1 so **ALL TRIGGERS** which should fire for this condition must be included, and since you did not specify an `OF <column_list>` no UPDATE TRIGGERS on A1 are excluded thereby (so both triggers specified for UPDATE ON A1 are included).

Of those triggers, Trigger A1_101 does an UPDATE C1 so **ALL TRIGGERS** which should fire for this condition must be included, and since you did not specify an `OF <column_list>` then no UPDATE TRIGGERS on C1 are excluded thereby (so both triggers specified for UPDATE ON C1 are included).

Whether or not a particular `WHEN <expression>` allows entry into the "trigger program code" is a **runtime** decision, not a **compile time** decision.

However, one of those triggers (C1_110) cannot be compiled because it contains an error.  The error is returned to you and the statement is **not compiled** and is not run (there is nothing to run because it failed to compile).