SQLite Forum

can we use a for/while loop inside sqlite triggers ?
I agree that while loops would be good to have, since using recursion instead would be a bad idea.

Another thing is some way to produce results out of triggers on views. The existing design doesn't really support this, but perhaps an idea can be: Add a `CALL` statement which has a similar syntax to `INSERT INTO` (only replacing the words `INSERT INTO` with `CALL`, and disallowing a `RETURNING` clause), which is only allowed on views, not on real tables. However, different statements in a trigger might have different numbers of result columns, and there may need a way to specify the column names of the result.

Note that for some purposes involving needing results, an extension can be used (mentioned recently on SQLite forum). However, that has an optimization barrier, and cannot be used when you want the other features of triggers (including the ability to insert multiple rows at once and have the triggers fire for each one, executing multiple statements including writes). However, it is good for table-valued functions; you can use recursive CTEs to make many kinds of computations and this will work.

I think these are really two different things, anyways; there is no need to combine them (and cannot meaningfully be combined, unless they later allow `RETURNING` clauses where a `SELECT` statement is expected; in that case it would make sense to allow `CALL` too).